How to fix the #N/A error in Excel
#N/A means “not available”. Almost always, a lookup function (VLOOKUP, XLOOKUP, MATCH, HLOOKUP) searched for a value and didn’t find an exact match.
Why it happens
The value really isn’t there
You looked up order 1012 but the list stops at 1008. Check the lookup range covers every row, including new ones added at the bottom.
Extra spaces or invisible characters
“East” and “East ” look identical but don’t match. Clean the data with TRIM, or look up TRIM(value).
Numbers stored as text
A lookup value of 1006 won’t match the text “1006” imported from another system. Convert with VALUE, or Data → Text to Columns.
Approximate match on unsorted data
VLOOKUP without FALSE (or MATCH without 0) assumes sorted data and can return #N/A or the wrong row. Always ask for an exact match.
The lookup column is in the wrong place
VLOOKUP only searches the first column of its table. If your key is in column C, start the table at C, or use XLOOKUP.
How to fix it
Ask for an exact match
=VLOOKUP(1006,A2:F9,3,FALSE) → Omar
Show a friendly message when nothing is found
=IFERROR(VLOOKUP(1099,A2:F9,3,FALSE),"Not found") → Not found
With XLOOKUP, use its built-in fallback
=XLOOKUP(1099,A2:A9,C2:C9,"Not found") → Not found
Examples use this sample sheet of orders:
Tip: Prefer IFNA over IFERROR for lookups: it hides only “not found”, so a typo elsewhere in the formula still shows up.
Other Excel errors
- #VALUE!A formula got the wrong kind of data, usually text where it needed a number.
- #REF!The formula points at a cell that no longer exists.
- #DIV/0!A formula tried to divide by zero or by an empty cell.
- #NAME?Excel doesn’t recognise a word in the formula.
- #SPILL!A formula that returns several results has no room to spill into.
- #NUM!The math is impossible or a number is out of range.