Excel Practice
Lessons Lesson 52

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

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

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

Start with one that works. The consignment numbers are in column A. In D2, look up the destination of consignment 2043.

To begin, type it exactly:

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

D2
Row A B C D E F G
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. 1

    Start with one that works. The consignment numbers are in column A. In D2, look up the destination of consignment 2043. To begin, type it exactly: =VLOOKUP(2043,A1:C5,2,FALSE).

    Hint. The manifest is A1:C5 and Destination is its second column.

  2. 2

    D3 is showing #N/A. It is looking up the number in E3, and that consignment is not on this manifest at all. The one you really want is 2044. Rewrite D3 to look that up instead.

    Hint. Only the first argument needs to change.

  3. 3

    D4 is showing #N/A even though 2043 is clearly on the manifest. E4 was imported from another system and holds its number as text. It looks the same on screen, but it is not equal to the number. In D4, wrap the lookup value so it is read as a number.

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

  4. 4

    D5 asks for the weight of the Cardiff parcel and returns #N/A. It is searching the whole manifest, and VLOOKUP only ever looks in the first column of the range. Give it a range whose first column is Destination.

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

  5. 5

    The last cause, and the one that hides best. E6 holds a destination with a trailing space left by a copy-paste. In D6, get that consignment's weight.

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

  6. 6

    Sometimes #N/A is the right answer and just needs saying in words. In D7, look up consignment 9051. Have the cell read "Not on the manifest" when it is not found. IFNA takes the formula first and the fallback second.

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