#N/A error: what it means and how to fix it
The formula couldn't find what it was asked to look for.
#N/A means “not available”. It nearly always comes from a lookup (VLOOKUP, XLOOKUP, MATCH, INDEX/MATCH) whose search value isn't in the table. Sometimes that's expected (a new product with no price yet); often it's a tiny mismatch you can't see.
Common causes, ranked
- 1
The value really isn't in the lookup range
A typo, a code that was never added, or a range that stops one row too early.
Fix: Check the value exists in the first column (VLOOKUP) or the lookup range (XLOOKUP). Lock the table with
$so it doesn't slide when you fill the formula down.Shows the error
=VLOOKUP(B2,G2:J8,2,FALSE)Fixed
=VLOOKUP(B2,$G$2:$J$8,2,FALSE) - 2
Numbers stored as text (or the other way round)
1042the number and"1042"the text look identical but never match. Imports and copied data often store numbers as text.Fix: Make both sides the same type. Convert text to numbers with
VALUE, or format the cells as numbers and re-enter them.Shows the error
=VLOOKUP(A2,$G$2:$H$9,2,FALSE)Fixed
=VLOOKUP(VALUE(A2),$G$2:$H$9,2,FALSE) - 3
Extra spaces
"Mara "with a trailing space isn't"Mara". Spaces are invisible, so the lookup looks right but fails.Fix: Clean the value with
TRIM(or clean the column once and paste the values back).Shows the error
=XLOOKUP(A2,$F$2:$F$9,$G$2:$G$9)Fixed
=XLOOKUP(TRIM(A2),$F$2:$F$9,$G$2:$G$9) - 4
Approximate match where you needed an exact one
VLOOKUP's last argument defaults to approximate match. On unsorted data it can give
#N/Aor, worse, a wrong row.MATCHbehaves the same without its0.Fix: Add
FALSEas VLOOKUP's last argument, or0as MATCH's match type.Shows the error
=MATCH(D2,$A$2:$A$9)Fixed
=MATCH(D2,$A$2:$A$9,0) - 5
#N/A typed in, or NA() used as a placeholder
Someone marked missing data with
#N/Aor=NA(), and every formula that reads it shows#N/Atoo.Fix: Replace the placeholder with the real data once you have it.
See it live
Edit the formula in D2 and fix the error yourself.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | SKU | Qty | Product | SKU | Product | Price | Stock | ||
| 2 | 2001 | PL-104 | 2 | Monstera | PL-101 | Fern | $9.50 | 40 | ||
| 3 | 2002 | PT-210 | 1 | Terracotta pot | PL-104 | Monstera | $34.00 | 12 | ||
| 4 | 2003 | TL-301 | 3 | Watering can | PL-107 | Snake plant | $22.00 | 25 | ||
| 5 | 2004 | PL-101 | 1 | Fern | PT-210 | Terracotta pot | $12.50 | 60 | ||
| 6 | 2005 | SL-401 | 4 | Potting mix | PT-212 | Ceramic pot | $19.00 | 18 | ||
| 7 | 2006 | PL-104 | 1 | Monstera | TL-301 | Watering can | $18.00 | 9 | ||
| 8 | 2007 | PT-212 | 2 | Ceramic pot | SL-401 | Potting mix | $7.25 | 80 | ||
| 9 | 2008 | PL-999 | 1 | #N/A |
How to hide it safely
When “not found” is a normal outcome, catch only #N/A with IFNA, or use XLOOKUP's own if_not_found argument:
=IFNA(VLOOKUP(B2,$G$2:$J$8,2,FALSE),"Not found")Careful: Hide it only after you know why it happens. IFNA is safer than IFERROR here: it still shows other errors, such as #REF! from a broken range, instead of covering them with “Not found”.
Excel vs Google Sheets
#N/A works the same way in both. Google Sheets' FILTER also returns #N/A when nothing matches (Excel's shows #CALC!). IFNA is in Excel 2013 and later and in Google Sheets. In Excel, IFERROR needs both arguments; in Google Sheets the second is optional and leaves the cell blank when you skip it.
Sources
Practise this in 5 minutes
Fixing #N/A is taught in Lesson 15: VLOOKUP (Unit 4, Lookups), on a live sheet with instant feedback. Pro
Open “VLOOKUP”Need a quick reference? The formula cheat sheet is free to read and print.