Why VLOOKUP returns #N/A (and how to fix it)

What #N/A means in VLOOKUP

VLOOKUP searches the first column of a range for lookup_value. If it finds no match (for exact match) or cannot approximate (for approximate match), it returns #N/A.

Exact match (the usual case):

=VLOOKUP(E2, A2:C100, 3, FALSE)

Approximate match (requires the first column sorted ascending):

=VLOOKUP(E2, A2:C100, 3, TRUE)

If you expected a blank or a custom message instead of #N/A, wrap the formula (examples later).

Fix checklist (work top to bottom)

1) Confirm you really need an exact match

Most business lookups (SKU, employee ID, email) need FALSE / 0.

• Excel: FALSE or 0
• Google Sheets: FALSE or 0

If you omit the 4th argument, Excel treats it as approximate match (TRUE). That alone produces surprising #N/A or wrong matches.

2) Check the lookup value and the first column are the same kind of data

Common mismatch:

Quick tests:

=E2=A2
=LEN(E2)
=LEN(A2)
=ISTEXT(E2)
=ISNUMBER(A2)

Normalize text IDs:

=VLOOKUP(TRIM(E2), A2:C100, 3, FALSE)

Or coerce numbers stored as text (only when the cleaned text is truly numeric):

=VLOOKUP(VALUE(TRIM(E2)), A2:C100, 3, FALSE)

3) Make sure the lookup column is the leftmost column of your range

VLOOKUP cannot look to the left. If your key is in column B and the return value is in column A, #N/A (or the wrong design) is expected.

Better options:

• Reorder columns so the key is first, or
• Use INDEX/MATCH or XLOOKUP (Excel), or
• In Sheets, use FILTER / INDEX+MATCH.

4) Verify col_index_num

col_index_num is relative to the start of the table array, not the worksheet column letter. For range B2:F100, column B is index 1 and column F is 5.

5) Confirm the value exists

=COUNTIF(A2:A100, E2)

• 0 → VLOOKUP will #N/A (exact match)
• 1 → one match
• >1 → VLOOKUP returns the first match only

6) Watch hidden characters from CSV / copy-paste

Non-breaking spaces and line breaks break exact matches. TRIM helps with normal spaces; stubborn cases may need CLEAN or re-typing the key.

Example 1 — Exact product price lookup

Sheet layout:

Lookup SKU in E2:

=VLOOKUP(E2, A2:C100, 3, FALSE)

If E2 is W-100, result should be 12.5. If E2 is W-999, result is #N/A.

Return blank when missing:

=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "")

Custom message:

=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "SKU not found")

Excel and Google Sheets both support IFNA for this pattern. Prefer it over IFERROR when you only want to catch missing keys.

Example 2 — Numbers stored as text (the silent #N/A)

Symptom: You can see 1001 in both cells, E2=A2 is FALSE, VLOOKUP returns #N/A.

Fix on the lookup side:

=VLOOKUP(VALUE(TRIM(E2)), A2:C100, 3, FALSE)

Or fix the table column long-term: convert column A to numbers, or store both sides as text consistently.

Sample check:

1. Put number 1001 in A2, text "1001" in E2.
2. Run the original VLOOKUP → #N/A.
3. Run the VALUE(TRIM(E2)) version → match.

Approximate match: when #N/A is “correct”

Grade / tax bracket tables use approximate match. The first column must be sorted ascending. If lookup_value is below the first breakpoint, VLOOKUP returns #N/A.

=VLOOKUP(E2, A2:B6, 2, TRUE)

If that #N/A is unwanted, add a lower bound row (e.g. 0) or wrap with IFNA.

Excel vs Google Sheets notes

Sheets-friendly leftward pattern (ID in B, name in A):

=IFERROR(INDEX(A2:A100, MATCH(E2, B2:B100, 0)), "")

Common mistakes that keep #N/A alive

1. Forgetting FALSE and accidentally using approximate match.
2. Table array starting on the wrong column.
3. Extra spaces / text vs number mismatch.
4. Looking up a value that is not in the first column of the range.
5. Using IFERROR everywhere and hiding real #REF! / #VALUE! bugs—prefer IFNA for missing keys.