How to fix #N/A in Google Sheets
What #N/A means, the five usual causes, and the formula that fixes each.
1. The value really isn't there
Check the spelling and that the value exists in the lookup range. If it is genuinely missing, show a message instead of the error:
=IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found")2. Extra spaces
"P-104 " with a trailing space doesn't match "P-104". Clean the lookup value with TRIM:
=VLOOKUP(TRIM(E2),A2:C100,3,FALSE)3. Numbers stored as text
1042 as a number doesn't match "1042" as text. Convert the lookup value to match the column:
=VLOOKUP(VALUE(E2),A2:C100,3,FALSE)
=VLOOKUP(TO_TEXT(E2),A2:C100,3,FALSE)4. No exact match set
Without FALSE, VLOOKUP does an approximate match and can return #N/A or the wrong row on unsorted data. Add FALSE as the last argument:
=VLOOKUP(E2,A2:C100,3,FALSE)5. The lookup column isn't first
VLOOKUP only searches the first column of the range. If your IDs are in column B, start the range at B, or use XLOOKUP, which can look in any column:
=XLOOKUP(E2,B2:B100,D2:D100,"Not found")Fix it in the cell
In Chrome or Edge, NOVA Ink spots formula errors like #N/A and #REF!, explains what went wrong, and applies the fix in the cell when you press Apply. Ctrl+Z undoes it. Using Excel on Windows? See VLOOKUP returning #N/A in Excel.
Questions
What does #N/A mean in Google Sheets?
It means "not available": a lookup formula couldn't find the value you asked for.
How do I hide #N/A in Google Sheets?
Wrap the formula in IFNA, for example =IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found").
Why does VLOOKUP return #N/A when the value is there?
Usually extra spaces, a number stored as text, or a missing FALSE for an exact match.
Is XLOOKUP available in Google Sheets?
Yes. XLOOKUP works in Google Sheets and can search any column, with a built-in value to show when nothing is found.
Fix the formula, not your afternoon.
NOVA Ink helps in Chrome and Edge, right where you type. 7-day trial with 10 AI actions a day. No card needed.