Why Is My VLOOKUP Returning #N/A?

Cause 1: the key differs by a hidden character

The most common cause is a key that looks right but is not: a trailing space, a nonbreaking space from a web export, or a nonprinting character. If E2 holds "S-20 " with a trailing space, =VLOOKUP(E2,$A$2:$C$100,2,FALSE) fails even though S-20 is in the table. Compare =LEN(E2) with the length you expect to confirm it.

Clean the key inside the lookup: =VLOOKUP(TRIM(E2),$A$2:$C$100,2,FALSE). TRIM removes ordinary spaces; for a nonbreaking space add SUBSTITUTE(E2,CHAR(160)," ") and CLEAN for nonprinting characters. Cleaning the source column once is better than embedding repairs in every lookup.

Cause 2: numbers and text do not match

The text "123" and the number 123 can look identical while failing an exact lookup. Diagnose with ISNUMBER and ISTEXT, then convert the side with the wrong storage type: VALUE turns numeric text into a number, and joining &"" turns a number into text. Do not convert codes that need leading zeros. The table column and the lookup value must have the same type.

Cause 3: approximate match and a drifting range

If the fourth argument is TRUE or omitted, VLOOKUP assumes the first column is sorted ascending and returns the last value that is less than or equal to the key. On properly sorted data it returns #N/A only when the key is smaller than the first item. Otherwise it can silently return a wrong row for a key that is not in the table at all. Use FALSE for IDs, names, and codes.

A related #N/A comes from the range. Without dollar signs, A2:C100 becomes A3:C101 on the next row and gradually drops valid records. VLOOKUP also searches only the first column of table_array, so a key that sits in column B needs a different table start, XLOOKUP, or INDEX with MATCH.

Not every error is #N/A

A column index larger than the table width returns #REF!, not #N/A: with a three-column table, index 4 fails. An index below 1 returns #VALUE!. Telling these errors apart saves time, because #N/A points at the key or the match type, while #REF! points at the column index.

Once the cause is known, =IFNA(VLOOKUP(TRIM(E2),$A$2:$C$100,2,FALSE),"Not found") replaces only #N/A, which suits an expected missing key. IFERROR also hides broken references and invalid indexes. =XLOOKUP(E2,A2:A100,B2:B100,"Not found") has an explicit not-found argument, but absent or inconsistent keys still need to be corrected.