How to Fix the VLOOKUP #N/A Error in Excel
The #N/A error in VLOOKUP means the lookup value wasn't found. Here are the six most common reasons and exactly how to fix each one.
#N/A is the most common VLOOKUP error. It means Excel searched the first column of your table and genuinely couldn't find a match — or it looks like there's a match but something is preventing it from connecting. Here are the six causes, in order of how often they actually happen.
1. Extra spaces (most common)
Your lookup value says "Apple" but the table has "Apple " (with a trailing space). They look identical but they're not.
Fix: Wrap the lookup value in TRIM:
=VLOOKUP(TRIM(A2), B:D, 2, FALSE)Or clean the source data with Find & Replace → find a space → replace with nothing, then re-run VLOOKUP.
2. Text vs. number mismatch
Your lookup value is the number 42 but the table stores it as the text "42". They look the same in the cell but VLOOKUP treats them as different.
Check: Select a cell from each column and look at the alignment. Numbers align right by default, text aligns left.
Fix (number stored as text → convert to number):
=VLOOKUP(VALUE(A2), B:D, 2, FALSE)Fix (number in lookup → match text in table):
=VLOOKUP(TEXT(A2, "0"), B:D, 2, FALSE)3. Lookup value genuinely doesn't exist
The value in A2 simply isn't in the first column of your table. Check by using MATCH alone to confirm:
=MATCH(A2, B:B, 0)If this also returns #N/A, the value isn't there. Check for typos or whether you're looking in the right table.
4. Approximate match instead of exact match
If the fourth VLOOKUP argument is TRUE (or omitted), Excel uses approximate match — which returns wrong results when your data isn't sorted in ascending order.
Fix: Always add FALSE as the fourth argument:
=VLOOKUP(A2, B:D, 2, FALSE)5. Lookup column isn't the first column
VLOOKUP must search the leftmost column of your table range. If you're searching column C but your range starts at column B, Excel is looking in the wrong place.
Fix: Either start your range at column C, or switch to XLOOKUP or INDEX MATCH, which have no left-column requirement.
6. Wildcards needed for partial matches
You're looking up "Apple" but the table has "Apple Inc.". Exact match will fail.
Fix: Use a wildcard:
=VLOOKUP("*"&A2&"*", B:D, 2, FALSE)⚠ WATCH OUT
Wildcard VLOOKUP returns the first match it finds. If multiple rows contain the text, you'll get only one result — and not necessarily the one you want.
Suppress #N/A without fixing the cause
If some #N/A results are expected (not all lookup values will have matches), wrap in IFERROR or use XLOOKUP's built-in fallback:
=IFERROR(VLOOKUP(A2, B:D, 2, FALSE), "Not found")=XLOOKUP(A2, B:B, D:D, "Not found")The Griddy way
Debugging VLOOKUP errors usually means checking four or five things manually across two different columns. Just describe the situation:
"My VLOOKUP is returning #N/A for half the rows — the lookup values are order IDs and the table is on the Products sheet"
Griddy diagnoses the mismatch and fixes the formula.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.