Confident Cells

#N/A in Excel: what it means and how to fix it

20 November 2026 · 2 min read

#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.

ABCDEF
1CodePriceCodePrice
2P-104£6.50P-101£3.20
3P-109#N/AP-102£4.75
4P-104£6.50
5P-107£12.00
P-109 isn't in the price list, so B3 shows #N/A.

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

Your turn

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.

ABCDEF
1CodePriceCodePrice
2P-104
3P-101
4 P-107
5P-102
6P-104
7P-101£3.20
8P-102£4.75
9P-104£6.50
10P-107£12.00