Excel VLOOKUP Formula Not Calculating? Here’s How to Fix It

Errors & fixes · Updated November 21, 2026

If an Excel VLOOKUP formula is not calculating, first check that the cell is not formatted as Text, that calculation is set to Automatic, and that the formula uses the right lookup range and match type. A VLOOKUP may appear not to calculate because Excel is displaying the formula itself, keeping an old result, or returning an error such as #N/A.

Start with the result you see

Different symptoms point to different causes, so note exactly what appears in the cell before changing the formula. If you see the formula characters rather than an answer, Excel may be treating the entry as text or Show Formulas may be on. If a previous answer remains after the source data changes, check calculation mode. If the cell shows an error, the formula may be calculating correctly but not finding a match or may refer to an invalid column.

For a basic example, assume the lookup value is in E2, product IDs are in A2:A100, and product names are in C2:C100, with the full lookup table in A2:C100. The formula is =VLOOKUP(E2,A2:C100,3,FALSE). It searches for E2 in the first column of the range and returns the corresponding value from the third column. If E2 contains ID 1042 and that row has Product X in column C, the result is Product X.

  • Formula displayed: check the cell format, leading apostrophe, and Show Formulas.
  • Old answer displayed: check calculation settings and recalculate.
  • #N/A: check whether the key exists and whether its type and characters match.
  • #REF! or #VALUE!: inspect the table range and column index.

How VLOOKUP is supposed to work

VLOOKUP searches down the leftmost column of a table range for a value, then returns a value from another column on the same row. Its arguments are the lookup value, the table range, the number of the return column counted from the left edge of that range, and an optional match setting. In the example above, 3 means the third column of A2:C100, which is column C—not the worksheet’s third column.

For most everyday lookups, use FALSE for an exact match. If the final argument is omitted, VLOOKUP uses approximate-match behavior, which can return a plausible but incorrect result when the first column is not sorted as required. Exact match is usually the safer choice for product codes, employee IDs, invoice numbers, and names. VLOOKUP also searches only from left to right: the lookup key must be in the first column of the selected range, and the return column must be to its right.

These rules help separate a formula-structure problem from a calculation problem. A correctly entered formula can still return #N/A when no exact key exists, and Automatic calculation cannot fix a key that is absent from the first column.

Fix a formula that appears as text

If the cell shows something like =VLOOKUP(E2,A2:C100,3,FALSE) instead of a result, try these checks in order:

  1. Select the cell and open Home > Number Format. If it is set to Text, change it to General.
  2. Re-enter the formula so Excel parses it. Select the cell, press F2, then press Enter. On Mac, use Control+U to edit the active cell, then Return; the exact shortcut can depend on keyboard settings.
  3. Look for an apostrophe before the equals sign. If one is present, remove it and confirm the entry.
  4. Check whether Show Formulas is enabled. On Windows, Ctrl+` toggles the display of formulas and results. You can also use Formulas > Formula Auditing > Show Formulas. On Mac, the shortcut may vary by keyboard layout; use the Show Formulas command on the Formulas tab if available.

Changing Text to General does not always convert an existing entry automatically. Re-entering it is important. Also check that the first character is an equals sign, not an apostrophe or a space. A formula copied from a webpage or chat can include leading characters that are not obvious in the cell.

Check calculation mode and recalculate

In Microsoft 365 for Windows, go to Formulas > Calculation Options and select Automatic. In Manual mode, Excel can keep showing a previously calculated result until you request a calculation. This setting can affect other formulas in the workbook, not just VLOOKUP, so consider why Manual was selected before changing it in a large model.

To recalculate the workbook, press F9. To force a full recalculation of all formulas in all open workbooks, press Ctrl+Alt+F9 on Windows. On Mac, use the calculation commands available from the Formulas menu; shortcut behavior can differ by keyboard and Excel release. If the workbook is large or uses external links, a full recalculation may take time.

Calculation mode is commonly associated with a workbook, and opening another workbook can make the setting seem unexpected. If formulas in several sheets are stale, check the mode rather than editing each VLOOKUP. If only one formula is wrong while other dependent formulas update normally, investigate its inputs and references as well.

Find and correct #N/A results

#N/A usually means an exact-match lookup did not find the lookup value in the first column of the selected range. The displayed values may look identical but differ underneath. For example, the number 1042 and text 1042 are different values to Excel in many lookup situations. A trailing space, a nonbreaking space from a web export, or a hidden character can also prevent a match.

  • Confirm the lookup value actually appears in the first column of the lookup range.
  • Compare the source and lookup key types. Convert values consistently rather than changing only one side.
  • Check for leading or trailing spaces. TRIM can remove ordinary extra spaces from text, but it does not remove every nonprinting character.
  • Check that the lookup range starts in the column containing the key, not one column to its left or right.
  • Make sure the formula uses FALSE when you require an exact match.

For example, assume E2 contains an ID, A2:A100 contains IDs stored consistently as numbers, and C2:C100 contains names. If the value in E2 is text while the table IDs are numbers, standardize the data type. You can use Excel’s Convert to Number option when it appears, or use a deliberate helper column to normalize a messy imported list. Do not convert IDs to numbers blindly if leading zeros are meaningful: an ID such as 00142 may be an identifier, not a quantity.

When investigating, avoid hiding the error immediately. An IFERROR wrapper can replace an error with a friendlier message, but it does not repair a misspelled key or an incorrect range. After confirming the lookup is sound, with the lookup value in E2 and the table in A2:C100, you could use =IFERROR(VLOOKUP(E2,A2:C100,3,FALSE),"Not found"). This displays Not found for any error, including errors other than a missing match, so use it only when that behavior is appropriate.

Inspect the range and column number

A VLOOKUP result can be wrong even when the formula calculates. Count the return-column index from the first column in the table range, and confirm it points to the field you want. If the range is B2:D100, then B is column 1, C is column 2, and D is column 3. A column index larger than the number of columns in the range produces #REF!.

When copying a formula down a list, a relative range can shift unintentionally. With lookup values in E2:E20 and a fixed table in A2:C100, lock the table range so it stays in place. In the formula for row 2, use =VLOOKUP(E2,$A$2:$C$100,3,FALSE). The dollar signs keep the table’s row and column references fixed when the formula is filled down, while E2 adjusts to E3, E4, and so on. Press F4 while editing a reference on Windows to cycle through reference styles; Mac shortcut behavior varies, and dollar signs can also be typed directly.

For a growing list, an Excel Table can be easier to maintain than a fixed range because it expands when rows are added. Select the data and choose Insert > Table, then use a structured reference in the lookup formula. Check the table and column names carefully: a reference to the wrong table column is still a valid reference and may return the wrong field.

Resolve syntax and regional differences

Excel’s formula argument separator depends on regional settings. Many installations use commas, as in =VLOOKUP(E2,A2:C100,3,FALSE), while some use semicolons. If Excel rejects a formula pasted from another computer, the separator setting may be the reason; use the separator expected by your installation. The comma-based examples here follow standard English-language Excel syntax.

Check the number of arguments, commas or separators, parentheses, and the spelling of the function. A missing parenthesis or an index typed as text can cause an entry error. If a formula is accepted but produces the wrong answer, syntax is less likely to be the problem than the lookup range, match setting, or source data.

When to use a different lookup method

VLOOKUP remains useful when the key is in the leftmost column and the return field is to its right. Its fixed column number can become fragile if someone inserts or rearranges columns in the lookup range. XLOOKUP avoids that particular limitation by taking separate lookup and return ranges, and it can search in either direction. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not recognize it.

With the lookup value in E2, IDs in A2:A100, and names in C2:C100, XLOOKUP can return the exact match using =XLOOKUP(E2,A2:A100,C2:C100,"Not found"). The result is the corresponding name, or Not found if there is no match. If you must share a workbook with people using older Excel releases, check compatibility before replacing VLOOKUP. INDEX and MATCH are another established option, but changing functions will not solve mismatched data types or missing keys.

Frequently Asked Questions

Why is my VLOOKUP showing the formula instead of the answer?
Most often the cell is formatted as Text, the formula starts with an apostrophe, or Show Formulas is turned on. Set the cell to General, re-enter the formula, and check the Show Formulas command.

Why does VLOOKUP return #N/A when the value is in the table?
The visible values may differ as text versus numbers, contain extra spaces or hidden characters, or be in a different column than the first column of the selected range. Compare the actual keys and confirm the range starts at the lookup column.

Why does VLOOKUP show an old result?
Check Formulas > Calculation Options > Automatic. If the workbook is in Manual mode, use F9 to recalculate, or use Ctrl+Alt+F9 on Windows for a full recalculation.

Does VLOOKUP need FALSE at the end?
For an exact match, yes: include FALSE as the fourth argument. If you omit the match argument, VLOOKUP uses approximate-match behavior, which is unsuitable for many unsorted lists and can return an unexpected result.

Can VLOOKUP return a value from a column to the left?
No. The lookup key must be in the first column of VLOOKUP’s selected range, and the result column must be to its right. Use XLOOKUP, where supported, or another lookup method when you need to return a value from the left.

Why does my VLOOKUP work in one row but not after copying it?
The table reference may have shifted as the formula was filled down. Lock the range with dollar signs, for example $A$2:$C$100, while leaving the lookup cell relative so it changes from row to row.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.