▦ Sheetova

Fix VLOOKUP #N/A in Excel: 7 Checks and Examples

Diagnose VLOOKUP #N/A errors with exact-match examples, data type checks, whitespace fixes, and Excel formulas you can copy.

A VLOOKUP #N/A result means Excel did not find a match under the specified lookup rules. It does not necessarily mean the value is absent: the lookup may be aimed at the wrong column, the cells may store different data types, or whitespace may prevent an exact match.

Start with exact match

Use =VLOOKUP(E2,A2:C100,3,FALSE) for a product ID lookup. FALSE tells Excel to require an exact match. Omitting it requests an approximate match and can produce surprising results on unsorted tables.

Check whether the key exists

Use =COUNTIF(A2:A100,E2) to count matches. A result of zero indicates that Excel does not recognize an exact match in that range. Check that the lookup value is in the first column of your VLOOKUP table.

Match data types and whitespace

The text "00123" and the number 123 are not the same lookup key. Extra spaces and non-breaking spaces can also break matching. Try =TRIM(E2) for ordinary leading or trailing spaces; use =VALUE(TRIM(E2)) only when converting a numeric identifier is safe.

Check the return column index

In =VLOOKUP(E2,A2:C100,3,FALSE), index 3 refers to column C within the selected table. A column index larger than the table width returns #REF!, not #N/A.

Handle missing results intentionally

Use =IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found") to show a friendly message when the key is missing. Prefer IFNA over broad IFERROR if you want other spreadsheet errors to remain visible.

Test known rows

Check a known matching ID, a missing ID, and an ID stored as text. If all three behave as expected, fill the formula down. When copying, lock ranges with dollar signs such as $A$2:$C$100.

Try the matching tools

Build a formula with the Excel formula builder, VLOOKUP generator, or XLOOKUP generator. Browse the formula guides for related problems.

Frequently asked questions

Why does VLOOKUP show #N/A when the value exists?

The cells may have different types, hidden spaces, or the table array may start in the wrong column.

Should I replace VLOOKUP with XLOOKUP?

XLOOKUP is often easier in modern Excel, but switching functions does not fix incorrect or mismatched lookup data.