← Excel from Scratch: Formulas, Functions, and Data Analysis
Lesson
The #N/A error and reliable lookups
Understand what causes #N/A in lookups and fix it, making your lookup formulas robust.
What #N/A is and why it appears
The #N/A error: value not found
The #N/A error (“not available”) means that a lookup function (VLOOKUP, HLOOKUP, MATCH) couldn't find the lookup value in the specified range. It isn't a technical error in the formula — it's a signal that there's no match.
Causes vary. Most often it's a typo in the lookup value or in the table. Leading or trailing spaces are invisible: “A-001” and “A-001 ” look alike, but a lookup treats them as different. Mismatched data types break lookups too: the number 42 and the text “42” (this sometimes happens after an import) don't match. Or the lookup value isn't in the VLOOKUP range's first column, or the range shifted when copied because $ signs were missing.
To fix it: make sure you use an exact match (fourth argument 0), check the range and lock it with $, remove extra spaces with TRIM, and make sure the data types match. If the error is acceptable (the product really may be missing from the lookup table), wrap the formula in IFERROR: =IFERROR(VLOOKUP(…), "Not found"). IFERROR doesn't fix real errors in the data, so deal with the cause first, and only then hide #N/A.
Lesson notes
The #N/A error: value not found
The #N/A error (“not available”) means that a lookup function (VLOOKUP, HLOOKUP, MATCH) couldn't find the lookup value in the specified range. It isn't a technical error in the formula — it's a signal that there's no match.
Causes vary. Most often it's a typo in the lookup value or in the table. Leading or trailing spaces are invisible: “A-001” and “A-001 ” look alike, but a lookup treats them as different. Mismatched data types break lookups too: the number 42 and the text “42” (this sometimes happens after an import) don't match. Or the lookup value isn't in the VLOOKUP range's first column, or the range shifted when copied because $ signs were missing.
To fix it: make sure you use an exact match (fourth argument 0), check the range and lock it with $, remove extra spaces with TRIM, and make sure the data types match. If the error is acceptable (the product really may be missing from the lookup table), wrap the formula in IFERROR: =IFERROR(VLOOKUP(…), "Not found"). IFERROR doesn't fix real errors in the data, so deal with the cause first, and only then hide #N/A.