Sheets AcademySheets Academy
Premium
Data cleanup and VLOOKUP

Why VLOOKUP returns #N/A

The VLOOKUP formula was dragged down through all the orders, and almost every row got a price. But two rows show #N/A, "not available": that's how VLOOKUP answers when it can't find the key in the lookup table. The errors look the same, but their causes are different. Find out what's wrong with these item numbers:

Select both item numbers with #N/A one by one and compare them with the price list
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
Item
Price, $
Item
Product
Price, $
1002
1001
Headphones
80
'1004
1002
Speaker
120
1009
1003
Charger
25
1001
1004
Cable
10
1003
1005
Case
15
Sheet1
Select an order's item number, and a comparison with the price list appears below the sheet.

Item 1004 is in the price list, but in the order it's a number stored as text, and to VLOOKUP the text "1004" and the number 1004 are different values. Item 1009 isn't in the price list at all.

How to tell what's wrong

#N/A in VLOOKUP has three common causes. Each one has its own sign, and each is fixed differently:

What you seeCause and what to do
A number key is aligned left, the cell has a green cornerA number stored as text. Click Convert to Number
A text key looks exactly like the one in the tableAn extra space. Remove it with the TRIM function
The key isn't in the tableNothing to fix. Show a clear message with the IFERROR function

Convert to Number is hidden in the menu of the icon with an exclamation mark next to the cell. If many item numbers are stored as text, select the whole column: the menu item fixes them all at once.

A space in a text key

For a city, a last name or a code like KB-1004, one space at the end is enough for VLOOKUP to stop recognizing it. A whole column is cleaned with the TRIM function, and a single cell is easier to type again:

fx
A
B
C
D
E
F
1
2
3
4
5
6
City
Delivery, $
City
Delivery, $
Boston
Boston
15
Austin
Austin
20
Denver
Denver
25
Sheet1
In A3 there's a space after “Austin”, so VLOOKUP can't find it. Type “Austin” in A3 without the space: B3 shows 20.

The key really isn't there: IFERROR

Item 1009 isn't in the price list, and there's nothing to fix here. But in a report, #N/A looks like something broke, and it's clearer to say it plainly: "not in list". That's what the IFERROR function is for. It calculates a formula and, if the result is an error, shows a fallback answer instead.

The finished VLOOKUP, whole and without the = sign, goes in as the first argument, and the fallback text in quotes as the second. Just as AND goes inside IF, here one function ends up inside another:

=IFERROR(VLOOKUP(A2,$D$2:$F$6,3,0),"not in list")

Excel calculates a formula like this from the inside out: first VLOOKUP, then IFERROR looks at its answer.

Item in A2:
=IFERROR(VLOOKUP(A2,$D$2:$F$6,3,0),"not in list")
Price list lookup
120
In the cell
120
Speaker 1002 is in the price list. The lookup found the price without an error, and it went into the cell.

IFERROR hides any error, even one you could fix. On the third tab, cable 1004 ended up as "not in list" even though it's in the price list: its item number is stored as text. That's why you fix the keys first and only then wrap VLOOKUP in IFERROR.