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

Invisible characters, text-formatted numbers, 15-digit truncation and leading zeros. Four causes, four one-line diagnoses.

The value is right there. VLOOKUP says #N/A. In order of how often they actually happen:

1 · Invisible characters

A trailing space, a non-breaking space (CHAR(160)), or a zero-width character copied from a web page. "ABC123" is not "ABC123 ". Excel will not show you the difference.

=LEN(A2)                      ' compare against LEN(B2)
=TRIM(CLEAN(A2))              ' fixes spaces and control chars (not CHAR(160))
=SUBSTITUTE(A2,CHAR(160),"")  ' that one needs its own pass

In pandas the equivalent is df["id"].str.strip(), which does handle non-breaking spaces — Excel's TRIM does not.

2 · Text that looks like a number

Your id column is "1001" (text, left-aligned). The lookup table has 1001 (number, right-aligned). No match. This is the most common one after a CSV export, because exports happily write 001001 and drop the flag.

=A2*1                ' coerce to number (fails loudly on real text - that is useful)
=A2&""             ' or coerce the other side to text

Never coerce both sides blindly. Coerce deliberately, in one direction, and compare the count of matches before and after.

3 · Numbers truncated to 15 significant digits

Excel stores numbers as doubles: any id longer than 15 digits gets its tail replaced by zeros. A 19-digit transaction id becomes 1234567890123450000. Both sides are "the same" and neither matches the other file.

Fix at import: tell the importer the column is text (in Power Query, change the type to Text before any other step). If the data is already damaged the original digits are gone — re-export.

4 · Leading zeros

007821 stored as a number is 7821. Joining to a system that pads to six digits fails. Pad explicitly on both sides: =TEXT(A2,"000000") or df["id"].str.zfill(6).

Diagnose the whole file in one pass

Put these next to your column and filter for anything that is not zero:

=LEN(A2)-LEN(TRIM(A2))              ' leftover spaces
=IF(ISNUMBER(A2),"number","text")    ' dtype, check both sides
=SUMPRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>127))  ' non-ASCII present

If you would rather have the diagnosis done for you, CSV Doctor reports dtype per column, junk inside numeric columns and mojibake, and it is free — download it here.