VLOOKUP #N/A, XLOOKUP blank results, INDEX/MATCH failures, wildcard mismatches. Lookup errors are the single most-searched Excel issue on the web — and 90% of them come from just five causes.
Lookup functions are the most-used and most-error-prone category in Excel. VLOOKUP alone accounts for a third of all Excel error searches. The good news: the causes are predictable. Trailing spaces, casing mismatches, numbers-stored-as-text, wrong column offsets, and approximate-match on unsorted data — that's 90% of everything you'll ever hit.
The four you'll see most
#N/A · VLOOKUP
VLOOKUP can't find the value
Top 5 causes ranked: (1) exact-match flag missing (FALSE), (2) trailing spaces in either range, (3) numbers stored as text vs real numbers, (4) lookup value not in first column of table, (5) value genuinely doesn't exist.
Fix: always use FALSE for exact match. Wrap with TRIM(). Use IFERROR for fallback.
Cause: XLOOKUP returns 0 for a found-but-empty cell — which displays as blank. Also, XLOOKUP treats "" (empty string) differently from a genuinely empty cell. Third cause: match_mode set to approximate when you need exact.
Fix: check match_mode is 0 (exact). To force text, wrap in IF(XLOOKUP(...)=0, "", XLOOKUP(...)).
Cause: MATCH failed to find the value. Same top causes as VLOOKUP #N/A — trailing spaces, casing mismatches, numbers-vs-text, wrong match_type (0 for exact vs 1 for approximate).
Fix: verify match_type is 0. Use TRIM() on lookup ranges. Check for text-vs-number data type mismatches.
Cause: The values look identical but aren't. Invisible non-breaking space (CHAR(160)) from web pastes. Different casing (in case-sensitive functions). Number stored as text with leading zero. Different decimal separators from international data.
Fix: use =EXACT(A1, B1) to test true equality. If FALSE, wrap both in TRIM(CLEAN()).
Trailing or leading spaces — either in the lookup value or the lookup range. Fix with TRIM().
Numbers stored as text — imported CSVs and pasted data often store "123" as text. VLOOKUP won't match text "123" to number 123. Fix with VALUE() or bulk-convert with Data → Text to Columns → Finish.
Missing exact-match flag — VLOOKUP defaults to approximate match if you leave the last argument blank. Always use FALSE (or 0) for exact match unless you specifically want fuzzy matching.
Wrong column offset in VLOOKUP — the col_index counts from the lookup column, not from column A. Users get confused when they insert/delete columns and the count shifts.
Approximate match on unsorted data — approximate-match VLOOKUP requires the lookup column sorted ascending. Unsorted data with approximate-match returns wrong values silently — no error but wrong result.
Browse lookup errors by function
VLOOKUP Errors
6 errors
The classic and most-common lookup function issues.
Use =EXACT(A1, B1) where A1 is your lookup value and B1 is a cell you know should match. If FALSE, values differ.
If EXACT is FALSE: wrap both in TRIM(). If still FALSE, wrap in CLEAN(). If still FALSE, check for casing or invisible characters (CHAR(160), Zero-Width Space).
If EXACT is TRUE but lookup still fails: check data types. =ISNUMBER(A1) vs =ISNUMBER(B1) — one may be text, one may be number.
If data types match: verify the lookup range starts at the correct column. VLOOKUP requires lookup value in the first column of the specified range.
If range is correct: confirm exact-match flag is set — FALSE for VLOOKUP/HLOOKUP, 0 for MATCH, 0 for XLOOKUP.
Last resort: use the Add-in's Error Explainer or paste into our free tool for automated diagnosis.
Prevention patterns
Always wrap VLOOKUP in IFERROR:=IFERROR(VLOOKUP(...), "Not found")
Prefer XLOOKUP when available: has built-in "if not found" argument, no wrapping needed
Wrap lookup ranges in TRIM at ingestion: when importing data, clean first, look up later
Convert to Excel Tables: Table structured references prevent column-offset drift
Test with edge cases: what happens when the lookup value doesn't exist? What if it's blank?
Add data validation: restrict what users can type into lookup input cells
Frequently asked questions
My VLOOKUP shows #N/A but I can see the value in the table — why?
99% of the time it's a data mismatch you can't see with your eyes. Test with =EXACT(lookup_value, target_cell). If it returns FALSE, the values differ in some invisible way (spaces, non-breaking space, casing, or text-vs-number).
Should I switch from VLOOKUP to XLOOKUP?
Yes, if you're on Excel 365 or 2021+. XLOOKUP is easier to write, safer, more powerful. Only stick with VLOOKUP if you need to share workbooks with Excel 2019 or earlier users.
Why does my lookup return #N/A only sometimes?
Some values match, some don't. Filter your data to show only the failing rows and check what's different about them. Usually you'll find spaces or data type inconsistencies in exactly those rows.
How do I lookup with multiple criteria?
Modern approach: =FILTER(return_col, (col1=x)*(col2=y)). Legacy: =INDEX(range, MATCH(1, (col1=x)*(col2=y), 0)) as an array formula. Or concatenate criteria columns into a helper column and VLOOKUP against that.
Can I search left-to-right in VLOOKUP?
No — VLOOKUP only searches the first column of its range. To lookup left of the return value, use INDEX/MATCH or XLOOKUP, both of which handle any direction.
Never debug a lookup manually again.
The Add-in's Error Explainer identifies exactly why your lookup fails — trailing spaces, type mismatch, or column offset — and gives you the fix.