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

#N/A means not available: a lookup function (VLOOKUP, XLOOKUP, MATCH, HLOOKUP) searched for a value and didn’t find it. Sometimes that’s true, and the value really is missing. More often the value is there but differs in a way you can’t see, such as a trailing space or a number stored as text. Find which case you have, fix the data, and only then decide whether to hide the error with IFNA.

1. The value really isn’t there

A-199 isn’t in the list, so the lookup has nothing to return. This is the one case where #N/A is the right answer, and the fix is to decide what to show instead.

F2fx=XLOOKUP(E2,A2:A6,C2:C6)

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	A-101	Keyboard	$49.00		A-199	#N/A
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

With XLOOKUP, put the fallback in its fourth argument. With VLOOKUP or MATCH, wrap the formula in IFNA:

2. A space you can’t see

E2 holds A-103 with a space at the end, often left behind by a copy from another system. To Excel, A-103 and A-103 are different text.

F2fx=XLOOKUP(E2,A2:A6,C2:C6)

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	A-101	Keyboard	$49.00		A-103	#N/A
3	A-102	Mouse	$19.00
4	A-103	Monitor	$189.00
5	A-104	Webcam	$59.00
6	A-105	Headset	$79.00

Check with =LEN(E2): 6 characters for a 5-character code gives it away. Clean the value inside the formula with TRIM, or clean the column once with Find and Replace.

3. A number stored as text

The IDs in column A are numbers; the 1052 in D2 is text (left-aligned, often with a small green triangle). 1052 the number and “1052” the text never match.

E2fx=XLOOKUP(D2,A2:A4,B2:B4)

Row	A	B	C	D	E
1	ID	Name		Find	Name
2	1041	Ana Kola		1052	#N/A
3	1052	Ben Ortiz
4	1063	Chloe Park

Turn the text into a number inside the formula with --D2 (or VALUE), or fix the cell with Data › Text to Columns › Finish.

4. VLOOKUP without FALSE

Leave out VLOOKUP’s fourth argument and it does an approximate match, which assumes the first column is sorted. On unsorted data it can return #N/A for a value that’s there, or worse, the wrong row. Always end it with FALSE: =VLOOKUP(E2,A2:C6,3,FALSE).

5. The range doesn’t reach the row

A lookup over A2:A5 can’t find a code on row 6. This happens when rows are added below a fixed range. Turn the data into a table (Ctrl+T) so ranges grow with it, or extend the range.

6. Searching the wrong column

VLOOKUP only searches the first column of its table. If the codes are in column B and the table starts at A, every lookup misses. Start the table at the column you search, or use XLOOKUP, which takes the search column as its own argument.

Practise it: return a price, or “Not listed”

Your task: In F2, return the price of the code in E2, or the text "Not listed" when the code isn't there.

Graded live

F2fx

Row	A	B	C	D	E	F
1	Code	Product	Price		Find	Price
2	B-201	Desk	$240.00		B-207
3	B-202	Chair	$135.00
4	B-203	Lamp	$42.00
5	B-204	Shelf	$88.00

Write the formula in the bar above, then press Check or Enter.

Hint

Look up E2 in A2:A5 and return from C2:C5. Then add "Not listed" as XLOOKUP's fourth argument, or wrap the lookup in IFNA.

Solution

=XLOOKUP(E2,A2:A5,C2:C5,"Not listed") Any formula that returns the same result passes.

Check

IFNA or IFERROR?

Both can hide #N/A, but IFERROR hides every error. If a typo turns XLOOKUP into XLOKUP, IFERROR quietly shows “Not listed” and you never learn the formula is broken. IFNA only replaces #N/A, so real mistakes still show. Use IFNA for lookups.