Why your VLOOKUP returns #N/A when the value is clearly there

You can see the value. It is right there in column B. And VLOOKUP still returns #N/A. Here is what is actually happening, in rough order of how often it turns out to be the cause.

1. Trailing whitespace

By far the most common. A value copied from a web page, an ERP export or a PDF frequently carries a trailing space, a non-breaking space (U+00A0), or a zero-width character. Visually identical. Not equal.

Test it directly:

=LEN(A2)=LEN(TRIM(A2))

If that returns FALSE, whitespace is your problem. TRIM alone will not remove a non-breaking space — you need:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

Or run the column through the Excel Data Cleaner, which strips all of them in one pass.

2. Numbers stored as text

The lookup value is the number 1024. The table holds the text "1024". Excel does not consider these equal, and there is no warning.

Check with:

=ISTEXT(A2)

If one side is text and the other numeric, coerce both. Multiplying by 1 or adding 0 converts text to number; concatenating with "" does the reverse.

3. Approximate match you did not ask for

This one silently returns *wrong answers* rather than #N/A, which makes it more dangerous. If you omit VLOOKUP's fourth argument, it defaults to TRUE — approximate match. On unsorted data, that returns whatever it happened to land on.

Always write the FALSE:

=VLOOKUP(A2,$B$2:$D$500,3,FALSE)

Better still, use XLOOKUP, which defaults to exact match and has no fourth-argument trap:

=XLOOKUP(A2,$B$2:$B$500,$D$2:$D$500,"Not found")

4. The lookup column is not the first column

VLOOKUP can only search the leftmost column of its table range. If your key sits in column C and you want something from column A, VLOOKUP simply cannot do it. Use INDEX/MATCH or XLOOKUP, both of which look in any direction.

5. The ranges shifted when you filled down

Without \$ anchors, \$B\$2:\$D\$500 becomes B3:D501 on the next row, then B4:D502, and eventually your table range slides past the data entirely. The Formula Checker flags this specifically because it produces #N/A only in the lower rows, which makes it hard to spot.

The diagnostic order that saves time

1. =EXACT(A2,B2) — tells you whether two specific values match character for character.
2. =LEN(A2)&"/"&LEN(B2) — length mismatch means hidden characters.
3. =ISTEXT(A2)&"/"&ISTEXT(B2) — type mismatch.
4. Check for the missing FALSE.
5. Check the anchors.

Work through those five in order and you will find the cause almost every time. If lookups keep failing across a whole dataset rather than a few rows, the data itself needs cleaning before any formula will behave.