Why VLOOKUP Returns #N/A

About this lesson

#N/A means VLOOKUP searched and did not find a match. There are only four reasons that ever happens.

By the end you can

name the four reasons a VLOOKUP returns #N/A.
fix a lookup that misses because of text-stored numbers or a trailing space.
write a lookup that says Not found instead of showing an error.

Practises VLOOKUP VALUE TRIM IFNA XLOOKUP

By Ali, Founder, Excel Lessons

The idea

Four, and the lesson makes you fix each one. The value really is not in the list. It is there as a number stored as text, which looks the same on screen and is not equal to the number you typed. It is there with a trailing space from a copy-paste, which looks the same too. Or the column you are searching is not the first column of the range you gave. VLOOKUP only ever searches the first one. That is the whole set. #N/A is also the only Excel error that is often correct. Sometimes the answer really is "not on the list". A sheet that says so in words is better than one showing an error code to somebody who does not read error codes.

The mistake to watch for

Using IFERROR before finding out why. #N/A from VLOOKUP has four usual causes. The value really is absent. It is a number stored as text on one side only. The lookup column is not the first column of the table. Or a trailing space makes two identical values unequal. Three of those are fixable. Only the first deserves a friendly message. IFNA, not IFERROR, is the wrapper that leaves the others visible.

Where this comes up again

XLOOKUP Lookups
VLOOKUP Lookups
HLOOKUP Lookups
INDEX Lookups

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

The consignment numbers are in column A.

In `D2`, type:

=VLOOKUP(2043,A1:C5,2,FALSE)

That is the destination of consignment 2043.

Row	A	B	C	E
1	Ref	Destination	Weight kg
2	2041	Leeds	2.4
3	2042	Bristol	1.1	9051
4	2043	Cardiff	4.75	2043
5	2044	Norwich	0.9
6				Bristol
7
8

Every step

1

The consignment numbers are in column A. In D2, type: =VLOOKUP(2043,A1:C5,2,FALSE). That is the destination of consignment 2043.

Hint. The table is A1:C5. Destination is column 2.
2

D3 shows #N/A because it looks up E3, and 9051 is not on the manifest. In D3, look up 2044 instead.

Hint. Only the first argument needs to change.
3

D4 shows #N/A even though 2043 is on the manifest. E4 was imported and holds 2043 as text, not a number. In D4, wrap E4 in VALUE so the lookup sees a number.

Hint. The text needs to become a number before the lookup sees it.
4

D5 looks up Cardiff and shows #N/A. VLOOKUP only searches the first column of its range. In D5, give it a range that starts at the Destination column. Remember: count the return column from the start of the range.

Hint. Start the range at the Destination column, then count the columns again from there.
5

The last cause hides best. E6 holds a destination with a space on the end. In D6, look up the weight for the destination in E6. Shape: =VLOOKUP(TRIM(cell), table, col, FALSE).

Hint. Combine what you did in the last two steps.
6

Sometimes #N/A is the right answer, and it needs saying in words. In D7, look up the destination of 9051 with IFNA around the lookup. Shape: =IFNA(lookup, if_not_found). Show Not on the manifest when it is not found.

Hint. Write the lookup first, then wrap it: IFNA( lookup , "message" ).