When VLOOKUP Does Not Work: Common Causes and Fixes

VLOOKUP usually fails because its lookup value does not exactly match the first column of the selected range, the range or column number is wrong, or Excel is using approximate matching. Check the formula’s match mode first: for a normal exact lookup in Excel for Microsoft 365 on Windows, use FALSE as the fourth argument, then verify the lookup column, data types, and spelling.

How VLOOKUP works—and where it can fail

VLOOKUP searches down the leftmost column of a range and returns a value from another column in the same row. Its four arguments are the value to find, the table range, the number of the result column counted from the range’s left edge, and the match mode. If your table is in A2:C100 and the value to find is in E2, column number 3 means the third column of that selected range—column C—not the third column of the worksheet.

With product IDs in A2:A100, product names in B2:B100, prices in C2:C100, and the ID to find in E2, this formula searches for an exact match and returns the price. If E2 contains P-104 and that row’s price is 12.50, the result is 12.50:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

VLOOKUP only searches the first column of its table range, and it returns a value to the right of that column. It also returns the first matching row if the lookup column contains duplicates. These limits are important: a formula may calculate without an error and still return a result that is not the one you intended.

Check the match mode before changing the data

The fourth argument controls whether Excel looks for an exact match or an approximate one. If you omit it, VLOOKUP defaults to approximate matching. That default is a frequent reason for unexpected results: Excel may return a nearby value rather than report that the requested value is missing. For approximate matching, the first column must be sorted in ascending order; otherwise, the result can be unreliable.

For most lists of names, product codes, invoice numbers, or employee IDs, use FALSE for an exact match. An exact lookup does not require the first column to be sorted. Use TRUE only when approximate matching is intended, such as finding a tax band or commission rate from a sorted set of thresholds. In that case, VLOOKUP returns the value associated with the largest threshold that is less than or equal to the lookup value.

For example, if a rate table has thresholds of 0, 1,000, and 5,000, an approximate lookup for 3,200 can return the row for 1,000. It is not searching for the nearest number in either direction. If the value must match exactly and is absent, the expected result is usually #N/A, not a nearby row.

Make sure the lookup column and range are right

The leftmost column in table_array must contain the values being searched for. If employee IDs are in column C but the selected range starts at column A, VLOOKUP searches column A instead. Select a range beginning with the lookup column, or rearrange the data if necessary. VLOOKUP cannot search one column and return a value from a column to its left.

Next, check the column index. It is a count within the selected range, starting at 1 for the first column. An index of 4 is invalid if the range contains only three columns. An index greater than the range width produces #REF!; an index less than 1 produces #VALUE!. A valid index can still point to the wrong field, so confirm that the selected return column is the one you actually want.

When copying a formula down a sheet, lock a fixed lookup table with dollar signs so the range does not slide. In $A$2:$C$100, both the columns and rows stay fixed when copied. Without those references, a formula copied one row down might search A3:C101 instead of A2:C100. Also inspect the range’s bottom row: a new record added beneath it is not included unless the range expands.

Look for mismatched data types, spaces, and characters

Values that look alike on screen may not be equal to Excel. A numeric ID such as 104 and text containing the characters 104 are different values. Changing a cell’s number format does not convert text into a number; formatting changes how a value appears, not the underlying type. If one side of the lookup is numeric and the other is text, an exact lookup may return #N/A.

Choose one consistent type for the lookup column and lookup value. If the source column contains numbers stored as text, convert the source data carefully or convert the lookup value when that is appropriate. With numeric IDs in A2:A20 and the numeric text 104 in E2, for example, this formula converts E2 to a number before searching. If 104 is present and the corresponding return value is North, it returns North:

=VLOOKUP(VALUE(E2),$A$2:$B$20,2,FALSE)

This conversion is not suitable for every identifier. A code such as 00104 may need to remain text because its leading zeros are meaningful. In that case, make both sides text in a consistent way rather than converting the code to a number, which would remove those zeros.

Leading or trailing spaces can also prevent a match. So can nonprinting characters copied from websites or other systems. Compare values in the formula bar, inspect their lengths, and clean a copy of the source data if needed. Excel’s TRIM removes ordinary extra spaces, but it does not remove every character that may be present in imported text; a nonbreaking space is a common example. Dates can have a similar issue: a real Excel date is stored as a number, while a date-looking text string is text.

Understand the error or unexpected result

#N/A usually means an exact match was not found, or that the lookup value and lookup column differ in type or content. Check for spaces, punctuation, leading zeros, and whether the value is actually in the selected range.
#REF! commonly means the column index asks for a column outside the selected range, or that a referenced column was deleted.
#VALUE! can indicate an invalid column index, such as an index below 1. Check the arguments as well as any referenced cells.
#NAME? can mean the function name is misspelled or text in the formula is not entered as a valid text value. Check spelling and quotation marks if the formula includes text.
A plausible but wrong result often points to an omitted match argument, an approximate match on unsorted data, a wrong column index, or duplicate lookup values.

Use IFNA if you want a more readable message when an exact match is genuinely absent. With product IDs in A2:A100, descriptions in B2:B100, and the requested ID in E2, this returns Not found when there is no match; if the matching description is Widget, it returns Widget:

=IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found")

This changes the displayed response, not the underlying lookup. It can hide a useful clue if the real problem is inconsistent data, so use it after checking the source values rather than as a substitute for diagnosis.

Use a practical troubleshooting sequence

Work through the formula and its inputs in a consistent order. This is usually faster than rebuilding the lookup or changing several parts of the workbook at once:

Read the formula from left to right and confirm the lookup value is the cell you intended.
Check that the selected range begins with the lookup column and includes the desired return column and all relevant rows.
Confirm the column index by counting from the left edge of that range.
Set the match argument explicitly. Use FALSE for an exact lookup unless you have a deliberate reason to use approximate matching.
Compare the lookup value with a source value in the formula bar. Check type, spaces, punctuation, and leading zeros.
If the formula was copied, verify that its range has not shifted and that the lookup table includes recently added rows.
If the displayed answer appears stale, check calculation settings. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options > Automatic. Excel for the web normally recalculates formulas automatically.

One useful distinction: a filtered-out or hidden row is not automatically excluded from VLOOKUP. If its value matches, VLOOKUP can still return a result from that row. A lookup that seems to ignore the visible filter may therefore be working as designed; the function searches the referenced range, not only the rows currently displayed.

When another lookup function is a better fit

VLOOKUP remains useful for straightforward tables, especially when the lookup key is in the first column and the return value is to its right. Its layout limits matter when the key is not at the left, when columns may be inserted, or when you need to return a value in either direction.

In Microsoft 365, XLOOKUP can search one range and return a corresponding value from another, so the lookup column does not have to be the first column of a larger table. With IDs in A2:A20, names in B2:B20, and the requested ID in E2, the following formula returns the matching name or Not found:

=XLOOKUP(E2,A2:A20,B2:B20,"Not found")

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If a workbook must support an older Excel version, check compatibility before replacing VLOOKUP. Another option is INDEX with MATCH, which can look up values when the return column is to the left; it is available in older Excel versions as well. Whatever method you choose, clean and consistent lookup data still matters.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is in the table?
It may be stored as text on one side and as a number on the other, include an extra space or hidden character, or fall outside the formula’s selected range. Check the exact source cell and use FALSE for an exact lookup.

Why does VLOOKUP return the wrong row?
Check whether the fourth argument is missing or set to TRUE. Approximate matching can return a threshold row, and it requires the lookup column to be sorted ascending. Also check for duplicate keys: VLOOKUP returns the first matching row.

Can VLOOKUP search to the left?
No. It searches the leftmost column of its table range and returns a value from a column to the right. Use XLOOKUP or INDEX with MATCH when the return column is left of the lookup column.

Does VLOOKUP need a sorted table?
Not for exact matching with FALSE. Sorting is required for dependable approximate matching with TRUE; the first column must be in ascending order.

Why does a VLOOKUP formula show as text instead of calculating?
The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula. Also check that Excel is not displaying formulas instead of results.

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.