#N/A in Excel: what it means and how to fix it
#N/A means "not available": a lookup (VLOOKUP, XLOOKUP, MATCH) couldn't find what you asked for. Either the value really isn't there, or it is there but doesn't match exactly, usually because of an extra space or a number stored as text.
A worked example
A price list has product codes in E and prices in F. You look up the code in A2 with VLOOKUP:
=VLOOKUP(A2,$E$2:$F$5,2,FALSE)
P-104 is in the list, so it brings back £6.50. P-109 isn't, so it shows #N/A. That #N/A is telling you something true.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Price | Code | Price | ||
| 2 | P-104 | £6.50 | P-101 | £3.20 | ||
| 3 | P-109 | #N/A | P-102 | £4.75 | ||
| 4 | P-104 | £6.50 | ||||
| 5 | P-107 | £12.00 |
The five usual causes
1. The value really isn't there
Check the spelling, then decide what should show instead. IFERROR or IFNA can show "Not found", and XLOOKUP has its own fourth part for it.
2. Extra spaces
"P-104 " with a space on the end isn't the same as "P-104". Pasted and exported data often has them, and you can't see them. Wrap the value in TRIM: =VLOOKUP(TRIM(A2),$E$2:$F$5,2,FALSE). More in cleaning text with TRIM.
3. A number stored as text
1042 the number and "1042" the text don't match. A small green triangle in the corner of the cell is the clue. Turn the text into a number first: see numbers stored as text.
4. The table moved when you copied down
Without $ signs, the table slides down a row each time, and the first rows drop out. Lock it with F4: $ and F4.
5. FALSE left off the end
Without FALSE, VLOOKUP looks for the nearest match in a sorted list, and can give #N/A or, worse, the wrong price. Always end with FALSE for an exact match.
Should you hide #N/A?
Only once you know why it's there. Hiding it with IFERROR before you've checked for spaces or text numbers just hides real mistakes.
Try it now
The codes in column A were pasted from an email, and some have extra spaces you can't see. In B2, look up each price from the list in E7:F10 so that none shows #N/A. Your formula will be copied down to B6.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Price | Code | Price | ||
| 2 | P-104 | |||||
| 3 | P-101 | |||||
| 4 | P-107 | |||||
| 5 | P-102 | |||||
| 6 | P-104 | |||||
| 7 | P-101 | £3.20 | ||||
| 8 | P-102 | £4.75 | ||||
| 9 | P-104 | £6.50 | ||||
| 10 | P-107 | £12.00 |