#N/A Error in Google Sheets
What it means: A lookup found no match — the search value isn't in the range being searched.
Quick fix
=IFNA(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not found")
IFNA replaces only #N/A with your own text, leaving other errors visible.
Why you see #N/A — and how to fix each cause
1. The lookup value genuinely isn't there
VLOOKUP returns #N/A when the value in the first argument doesn't exist in the lookup column.
Fix: Check the source data actually contains the value, then handle real misses with IFNA.
2. Extra spaces or different capitalization
"Apple " with a trailing space won't match "Apple". Lookups match the whole cell.
Fix: Clean both sides with TRIM() so stray spaces don't block the match.
=VLOOKUP(TRIM(A2), Sheet2!A:B, 2, FALSE)
3. Approximate match on unsorted data
VLOOKUP's 4th argument defaults to TRUE (approximate), which needs a sorted column and often returns #N/A otherwise.
Fix: Add FALSE as the last argument to force an exact match.
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
Before and after
| Broken | Working |
|---|---|
=VLOOKUP(A2, Sheet2!A:B, 2) |
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE) |
Adding FALSE forces an exact match, the fix for most surprise #N/A results.
How to stop it happening again
Always pass FALSE for an exact lookup, TRIM your keys, and keep the lookup key column consistent in type (all text or all numbers). XLOOKUP has a built-in "if not found" argument that avoids #N/A entirely.
FAQ
Is #N/A a bug?
Not always. In a lookup it usually means the value truly isn't there. Use IFNA to show a friendly message instead of the raw error.
How do I hide #N/A in a chart?
Wrap the source formula in IFNA(...,"") or IFNA(...,NA()) depending on whether you want a gap or a blank.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas