Excel VLOOKUP Not Working? Fix the Common Causes

If VLOOKUP is returning an error or the wrong result, check that the lookup value is in the first column of the selected range, use the correct return-column number, and set the final argument to FALSE for an exact match. Then check for mismatched data types, extra spaces, duplicate keys, and references that shifted when you copied the formula.

How VLOOKUP works

VLOOKUP searches down the first column of a table range for a value, then returns a value from another column in the same row. Its arguments are the value to find, the table range, the number of the column to return from that range, and the match mode. The return-column number is counted from the left edge of the selected range, not from the worksheet’s column letters.

For example, if IDs are in D2:D100, names are in E2:E100, and prices are in F2:F100, the range D2:F100 makes D column 1, E column 2, and F column 3. With the ID to find in A2, this formula returns the price for an exact ID match:

With the lookup ID in A2 and the ID, name, and price table in D2:F100, enter =VLOOKUP(A2,$D$2:$F$100,3,FALSE). If A2 contains ID-104 and the matching row has 125.50 in column F, the result is 125.50.

VLOOKUP can only search the leftmost column of its selected range and return a value from a column to its right. It also returns the first matching row it encounters, so duplicate lookup values can produce a result that seems wrong even when the formula is working as designed.

Start with the match mode

For most lists of names, product codes, invoice numbers, or IDs, use FALSE as the fourth argument. FALSE requests an exact match. If you leave this argument out, VLOOKUP uses approximate matching, which can return an unexpected value when the first column is not sorted appropriately.

TRUE requests approximate matching. In that mode, the first column should be sorted in ascending order. VLOOKUP finds the largest value that is less than or equal to the lookup value. This can suit a threshold table, such as tax bands or grade cutoffs, but it is not the right choice for an unsorted list of unique IDs. Approximate matching on an unsorted range can give a plausible-looking but incorrect result.

Exact matching is not case-sensitive: for example, Excel treats abc and ABC as equivalent for a VLOOKUP match. In exact mode, text lookup values can also use wildcards: an asterisk represents any sequence of characters, and a question mark represents one character. If those characters are part of a literal code rather than wildcards, account for that when building the lookup.

Check the lookup range and column number

Confirm that the lookup column is the first column in the table range. If the value to find is in column F but the selected range starts at D, VLOOKUP will search D, not F. It cannot look left within the range. One fix is to rearrange the source table or select a range whose first column contains the lookup key; another is to use XLOOKUP if it is available in your Excel version.

Next, count the return column from the start of the range. If the range is E:G, then E is 1, F is 2, and G is 3. A column number larger than the number of columns in the range produces #REF!. A column number below 1 produces an invalid argument error. Also make sure the range includes the column containing the result you want, rather than stopping one column too early.

Watch for a common misconception: the third argument is not the worksheet column number. If the return value is in worksheet column F, the index is 3 only when the selected range starts in D. If the range starts in A, F is the sixth column in that range.

Look for text, number, and spacing mismatches

Two values can look identical on screen but be stored differently. A cell may contain the number 104 while the matching cell contains the text value 104. Number formatting does not change the underlying value, so applying a number format to a text value does not necessarily make it match a numeric lookup value. Check the cells’ alignment and number formats, and use a temporary helper column or a carefully chosen conversion to make both sides consistent.

With a text value such as 104 in A2, a helper formula in B2 can convert it to a number:

With the text-formatted number in A2, enter =VALUE(A2) in B2. For a value Excel recognizes as a number, the result is the numeric value 104. Verify that conversion suits your data before replacing or matching the original values.

Leading or trailing spaces are another frequent cause of #N/A. Imported data can also contain nonprinting characters. For a basic cleanup, if the source text is in A2, enter =TRIM(CLEAN(A2)) in a helper cell. CLEAN removes many nonprinting characters, and TRIM removes ordinary extra spaces while keeping single spaces between words. Compare the cleaned values on both sides of the lookup; cleaning only the lookup cell will not fix a dirty value in the table.

TRIM and CLEAN do not remove every character that may look like a space. Web data sometimes includes a nonbreaking space, which may need to be replaced explicitly. Dates can have a similar hidden mismatch: a date displayed as 1/2/2026 may be a real Excel date serial in one cell and text in another. Converting both sides to the same underlying type is more reliable than changing how they are displayed.

Understand common errors and wrong results

#N/A: An exact match was not found. Check spelling, spaces, data types, and whether the value is actually present in the first column of the range.
#REF!: The return-column index points beyond the selected range, or a referenced range was deleted or changed.
#VALUE!: Check that the column index is a valid positive number and that the formula arguments are in the intended positions.
#NAME?: Check the function spelling and any text arguments. Text written directly in a formula needs quotation marks; a cell reference does not.
A result that belongs to another row: Check whether approximate matching is enabled, whether the key is duplicated, and whether the range includes the intended rows.

If the formula has the right structure but the displayed result is stale, check calculation settings. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and choose Automatic if the workbook should recalculate automatically. A manual calculation setting can leave formulas showing old results until recalculation. This is different from a VLOOKUP matching problem, so first confirm that the formula itself points to the right cells.

Keep references steady when copying formulas

When you fill a formula down, a relative range moves with the formula. That can make the first few rows work while later rows search the wrong part of the table. In the example formula, dollar signs make the table range absolute, so it remains D2:F100 as the formula is copied. The lookup cell A2 stays relative and changes to A3, A4, and so on.

Inspect a formula in a row where the result is wrong and compare its references with a row that works. Check that the lookup cell changes as intended, the table range stays fixed, and the return index remains correct. If the table grows over time, consider converting it to an Excel Table using Insert > Table. A table can expand as rows are added, which helps avoid ranges that silently omit new records.

For troubleshooting, select the formula cell and use Formulas > Evaluate Formula to follow how Excel evaluates the references and match. You can also inspect the formula bar and select the range argument to see which cells are highlighted. These checks help distinguish a bad match from a wrong reference or index.

Handle missing results without hiding problems

Once the lookup works as intended, you may want a readable message instead of #N/A when a key is absent. IFERROR can do that, but it also hides other formula errors, such as #REF!, so do not use it as a substitute for fixing the underlying formula. For a message only when the lookup has no match, IFNA is more targeted.

With the lookup ID in A2 and the ID-to-price table in D2:E100, this formula displays Not found when there is no exact match and otherwise returns the price from the second column of the range:

=IFNA(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Not found")

If the ID exists and its price is 49.99, the result is 49.99; if the ID is absent, the result is Not found. Before adding this wrapper, check whether a missing result reflects a genuinely absent ID or a preventable mismatch such as text versus number.

When XLOOKUP or another method is a better fit

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Unlike VLOOKUP, it can search in one column and return a result from a column to either side, and its default match is exact. It also lets you specify a value to show when nothing is found. For an older workbook that must work in earlier Excel releases, VLOOKUP remains a familiar option, but its leftmost-column and column-index requirements still apply.

INDEX and MATCH are another option when you need more flexible lookup directions and compatibility with versions that do not include XLOOKUP. For a straightforward task, however, replacing VLOOKUP is not always necessary. If the problem is an extra space, an incorrect range, or approximate matching, changing the function will not automatically correct the source data or the lookup logic.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is in the table?
Check that the value is in the first column of the selected range and that the formula uses FALSE for exact matching. Then compare the underlying values for text-versus-number differences, extra spaces, and hidden characters. A value can look identical but still fail an exact match.

Why does VLOOKUP return the wrong value instead of an error?
Approximate matching may be enabled, the first column may not be sorted for approximate matching, or the key may appear more than once. With exact matching and duplicate keys, VLOOKUP returns the first match from top to bottom.

Can VLOOKUP look to the left?
No. It searches the first column of its selected range and returns a value from a column to the right. Rearrange the table, use INDEX and MATCH, or use XLOOKUP in a supported Excel version if the return column is to the left.

Does VLOOKUP distinguish uppercase and lowercase?
No. VLOOKUP matching is not case-sensitive, so values that differ only by letter case are treated as a match. If case must distinguish records, use a case-sensitive lookup approach rather than relying on VLOOKUP alone.

Why does a VLOOKUP formula work in one row but not after I copy it?
The table reference may have shifted as the formula was filled. Lock the table range with dollar signs, then check that the lookup reference changes row by row and that the column index still points to the intended return field.

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.