VLOOKUP Errors in Excel: Causes, Checks, and Fixes

Formulas & functions · Updated November 19, 2026

If VLOOKUP returns an error, first check that the lookup value is in the leftmost column of the selected range and use FALSE for an exact match. The error often comes from a mismatch in the data, an incorrect column number, or an omitted match argument—not from a broken Excel function.

How VLOOKUP works

VLOOKUP searches down the first column of a 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 return column within that range, and whether to use an approximate or exact match. The return-column number starts at 1 for the range’s first column; it is not the worksheet’s column letter.

For example, assume a price list has product codes in A2:A100, product names in B2:B100, and prices in C2:C100, while the code to search for is in E2. With that layout, this formula returns the matching price from column C:

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

If E2 contains a code found in A2:A100 and its price is 12.50, the result is 12.50. The dollar signs keep the table range fixed if you copy the formula to other cells. In Microsoft 365 for Windows, enter the formula in the result cell and press Enter. Excel for Mac uses the same formula; menu and keyboard details can vary, but the function’s arguments do not.

What the main VLOOKUP errors mean

The error value is a useful clue. Identify it before changing the formula, because each error points to a different class of problem.

  • #N/A: Excel did not find a match under the selected match rules. Check the lookup value, data types, spaces, and first column of the range.
  • #REF!: The return-column number points outside the selected range, or a referenced column was deleted.
  • #VALUE!: An argument is invalid—for example, the column number is less than 1, or the formula has an incompatible value in an argument.
  • #NAME?: Excel cannot interpret part of the formula, commonly because VLOOKUP is misspelled or text in a formula was not enclosed in quotation marks.

A result that looks wrong is not always an error value. VLOOKUP can return a valid but unexpected result when approximate matching is used on an unsorted list, when the wrong column number is supplied, or when the lookup value appears more than once.

Fix #N/A: check the lookup value and data

With exact matching, #N/A means Excel cannot find a value it considers equal to the lookup value in the first column of the table range. Start by confirming that the value is actually present in that column, not merely somewhere in the selected range. VLOOKUP searches only the leftmost column of its table array.

Next, compare the values carefully. A number stored as text is different from a numeric value, even when both display as 123. One clue is that numbers stored as text may be left-aligned by default, although cell formatting can change alignment. Check the source data and, where appropriate, convert the values so both sides use the same type. Do not convert identifiers such as product codes or postal codes to numbers if leading zeros are meaningful.

Hidden spaces are another common cause. A copied code might contain a trailing space, or imported text might include nonprinting characters. Compare the original cells in the formula bar, and clean the source data if necessary. A quick visual check may not reveal a space at the end of a value. Also check for different punctuation, hyphens, or leading zeros.

For exact text matching, capitalization alone is generally not the issue: VLOOKUP’s text comparison is not case-sensitive. However, a spelling difference or an extra character still prevents an exact match. If your values contain wildcard characters, remember that an asterisk can represent any sequence of characters and a question mark can represent one character in an exact-match lookup. A tilde is used to search for a literal wildcard character.

Check the fourth argument before changing the data

The fourth argument, often called range_lookup, controls the match type. Use FALSE when you need an exact match, such as a product ID, employee number, or account code. If you leave this argument out, VLOOKUP uses approximate matching by default. That default can produce a plausible-looking but incorrect answer rather than an error.

Approximate matching is useful for ordered bands, such as a rate table with defined thresholds. In that mode, the first column should be sorted in ascending order. Excel finds an exact match or, when there is no exact match, the next smaller value. If the first column is not sorted as required, the result can be unreliable. For ordinary lookups where the key should match exactly, specify FALSE explicitly so the formula’s intent is clear.

To illustrate, suppose A2:A4 contains the ascending thresholds 0, 10, and 20, and B2:B4 contains the associated labels Low, Medium, and High. With the lookup value 15 in E2 and that two-column table in A2:B4, approximate matching returns Medium, the label for the next smaller threshold, 10. If the same table is unsorted, do not rely on that result.

Fix #REF! and incorrect return values

The third argument, col_index_num, counts columns from the left edge of the selected range. If the range is A:C, column A is 1, B is 2, and C is 3. Entering 4 asks for a column that is not in the range and returns #REF!.

With the product list in A2:C100 described above, this formula asks for the fourth column of a range that has only three columns, so Excel returns #REF!:

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

A related problem occurs when someone inserts or rearranges columns. A hard-coded column number can still be valid but now point to a different field, producing a wrong result without an error. Recheck the table range and the column count whenever the source layout changes. Keep the lookup range tightly scoped to the intended fields, and label columns clearly so the return column is easy to verify.

Understand VLOOKUP’s range and duplicate limits

VLOOKUP cannot search to the left. The lookup key must be in the first column of the table array, and the return column must be to its right. Selecting a range that starts one column too far right can cause #N/A even though the key appears on the worksheet. Adjust the range so its left edge is the key column.

If a key appears multiple times in the first column, VLOOKUP returns the first match it encounters from the top of the range. It does not report a duplicate as an error or automatically return the last occurrence. If duplicates are possible, decide which record should win and either clean the source data, sort or filter it appropriately, or choose a lookup method that can return multiple records.

Also distinguish a blank-looking result from a failed lookup. VLOOKUP may successfully find a row whose return cell is empty, so the result appears blank. That is different from #N/A. Check the matching source row before assuming the formula failed.

A practical troubleshooting sequence

Use a consistent sequence instead of rewriting the formula at random. It helps isolate whether the problem is the formula structure, the lookup key, or the source table.

  1. Read the error value, or compare the returned value with the source row you expected.
  2. Confirm that the lookup value is in the leftmost column of the selected range.
  3. For a key that should match exactly, add or verify FALSE as the fourth argument.
  4. Count the columns in the selected range and check that the third argument points to the intended return column.
  5. Compare the key and source values for data type, leading zeros, spaces, spelling, and punctuation.
  6. Check whether the key occurs more than once and whether the matching return cell is blank.
  7. Test with a key you can verify manually, then adjust the formula or clean the source data based on what you find.

For instance, if the formula returns #N/A for a code you can see in the worksheet, first verify that the visible code is in the leftmost column of the formula’s range. Then compare the actual cell contents and types. Simply widening the range to include more columns will not fix a mismatch in the key itself.

When to use IFNA, XLOOKUP, or another method

IFNA can replace a genuine not-found result with a clearer message, but it should come after you have checked the lookup logic. With the product code in E2, codes in A2:A100, and prices in C2:C100, this formula displays Not found when there is no exact match; otherwise it returns the price:

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

IFNA handles #N/A specifically. That is usually preferable to hiding every possible error, since an invalid column number or broken reference should remain visible for correction. A message such as Not found is also more informative than a blank if the workbook’s users need to know that no match exists.

In Microsoft 365 and Excel 2021 or later, XLOOKUP is another option. It can search in either direction and separates the lookup range from the return range, so it avoids VLOOKUP’s leftmost-column and column-index limitations. It is not available in some older Excel versions, so check compatibility before sharing a workbook. INDEX and MATCH are another established alternative in versions where XLOOKUP is unavailable, though their formulas have a different structure.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visible?
Most often, the value is not in the first column of the selected range, or the lookup value differs from the source in data type, spaces, punctuation, or leading zeros. Check the exact formula range and compare the underlying cell contents.

Why does VLOOKUP return the wrong row?
Check whether the fourth argument is omitted or set to TRUE. That requests approximate matching, which depends on an ascending-sorted first column. For an exact key lookup, use FALSE. Also check for duplicate keys: VLOOKUP returns the first match.

What does the 3 mean in a VLOOKUP formula?
It is the return-column number counted from the first column of the selected table range. In a range beginning at A and ending at C, 3 refers to C. It does not mean worksheet column C in every formula; the range’s starting point matters.

Does VLOOKUP distinguish uppercase and lowercase?
No. Its text matching is not case-sensitive, so ABC and abc are treated as equivalent for a lookup. Extra spaces, different characters, or other spelling differences can still prevent an exact match.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the leftmost column of the table array, and VLOOKUP returns from a column to its right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or consider INDEX and MATCH when the return field is to the left.

Related

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.