How to Fix a VLOOKUP #N/A Error in Excel (Find the Cause)
The key is right there in both tables, yet VLOOKUP still says #N/A — because a space or hidden character you can't see is breaking the match. Here's why it's invisible, and the one-click fix.
Last updated: August 2026
| Key (looks fine) | Fixed | |
|---|---|---|
| Acme· | → | Acme (trailing space trimmed) |
| 1001 (as text) | → | 1001 (as number) |
Why the cause is invisible
A key can look identical on screen and still never match. The usual culprits are a trailing space, a non-breaking space (CHAR(160)) pasted from the web, another hidden character, or a number stored as text — "1001" the text won't equal 1001 the number. You can chase these with TRIM, CLEAN, SUBSTITUTE and VALUE helper columns, but first you have to guess which one is wrong, and the difference is near-impossible to spot by eye.
The 1-click way — Tellsheet's Match Doctor
Select the key column and run Match Doctor. For each failing key it diagnoses the cause — trailing or non-breaking space, hidden character, or text-vs-number — and repairs the value in place so the lookup matches, telling you exactly what it changed. It runs on your device, nothing uploaded. Pairs with Find Hidden Characters and Remove Extra Spaces.
Frequently asked questions
Why does VLOOKUP return #N/A when the value exists?
Usually an invisible difference — a trailing space, non-breaking space, hidden character, or number stored as text. Match Doctor finds it and repairs the value.
How do I find a trailing space I can't see?
Match Doctor checks each failing key for trailing and non-breaking spaces and hidden characters, then trims it so the key matches.
What about numbers stored as text?
A text "1001" won't match a real 1001. Match Doctor detects the mismatch and converts the value so both sides line up.
Does it change my data?
It repairs only the failing keys it diagnoses, in place, and runs locally — so you can see what was fixed and nothing is uploaded.
Related Excel guides
Fix your #N/A lookups in one click
Match Doctor diagnoses why each key fails — space, hidden character, text-vs-number — and repairs it so the lookup matches, offline.
Get Tellsheet free See pricing