How to Fix VLOOKUP #N/A Error — 7 Causes & Solutions

The #N/A error in VLOOKUP means "value not found." But 90% of the time, the value IS there — something else is wrong. Here are all 7 causes and their exact fixes.

Cause 1: Extra Spaces in Data

The problem: Your lookup value looks correct, but there are invisible trailing or leading spaces. "Delhi" and "Delhi " are different to Excel.

The fix:

=VLOOKUP(TRIM(A2), table, 2, FALSE)

Wrap your lookup value in TRIM(). Better yet, clean your entire source column first: put =TRIM(A2) in a helper column and VLOOKUP from that.

Cause 2: Number Stored as Text (or Vice Versa)

The problem: Your lookup column has "1001" as text, but your lookup value is the number 1001. Excel treats these as different.

How to spot it: Numbers stored as text align LEFT in their cell. Real numbers align RIGHT. You'll also see a small green triangle in the corner.

The fix:

=VLOOKUP(VALUE(A2), table, 2, FALSE)    — if lookup table has numbers
=VLOOKUP(TEXT(A2, "0"), table, 2, FALSE) — if lookup table has text

Or select the column → Data → Text to Columns → Finish (this converts text to numbers).

Cause 3: Approximate Match Instead of Exact Match

The problem: You forgot the 4th argument (or set it to TRUE). VLOOKUP defaults to approximate match, which requires sorted data.

The fix: Always use FALSE as the 4th argument for exact match:

=VLOOKUP(A2, B:D, 2, FALSE)
                              ↑ this is critical

Rule: In 99% of real work, you want exact match. Always type FALSE.

Cause 4: Lookup Value Doesn't Exist

The problem: The value genuinely isn't in your lookup table. Typo, missing entry, or different spelling.

The fix: Use IFERROR to handle it gracefully:

=IFERROR(VLOOKUP(A2, table, 2, FALSE), "Not Found")

But first, verify with =COUNTIF(lookup_column, A2) — if it returns 0, the value truly doesn't exist.

Cause 5: Lookup Column Is Not the First Column

The problem: VLOOKUP can only search the FIRST (leftmost) column of your table range. If your lookup value is in column C but your table starts at column A, it won't find it.

The fix: Either rearrange your table, or switch to INDEX-MATCH:

=INDEX(return_column, MATCH(lookup_value, search_column, 0))

INDEX-MATCH can look in any direction — no "first column" restriction.

Cause 6: Wrong Table Range

The problem: Your table_array doesn't include the lookup column, or you selected the wrong range.

The fix: Double-check that:

• Column 1 of your range contains the lookup values
• The range extends far enough to include the return column
• Lock the range with $ signs: $B$2:$D$100 (so it doesn't shift when you copy the formula down)

Cause 7: Wildcard Characters in Data

The problem: Your data contains * or ? which VLOOKUP treats as wildcards. "What?" matches "Whatever" unexpectedly.

The fix: Escape wildcards with a tilde: ~* and ~?

=VLOOKUP("What~?", table, 2, FALSE)

Quick Diagnostic Checklist

1. Is the 4th argument set to FALSE? (exact match)
2. Run =TRIM(A2)=A2 — does it return TRUE? (no extra spaces)
3. Are both values the same type? (both text or both number)
4. Is the lookup value in the FIRST column of your range?
5. Does =COUNTIF(lookup_column, A2) return > 0?

If all 5 checks pass, the VLOOKUP should work. If it still doesn't, copy-paste both values into Notepad to check for hidden characters.

Pro Tip: Stop Using VLOOKUP

Seriously. INDEX-MATCH is more reliable, flexible, and doesn't break when you insert columns. If you're getting frequent #N/A errors, switching to INDEX-MATCH will solve half of them automatically.

=IFERROR(INDEX(return_range, MATCH(lookup_value, search_range, 0)), "Not Found")