The most common cause of #N/A in VLOOKUPs: trailing spaces. "Acme Corp" and "Acme Corp " are different strings to Excel — even though they look identical in a cell. The second string has a space character after the text that you can't see.
The fix: Wrap both the lookup value and the lookup table's key column in TRIM() before using them.
Diagnosing the Problem
Before applying any fix, confirm that invisible characters are actually the issue:
Test 1: LEN() comparison
=LEN(A2)
Apply this to both the lookup value and the "identical" value in your lookup table. If the lengths differ, there's a hidden character.
Test 2: EXACT() comparison
=EXACT(A2, B2)
Returns TRUE only if both values are byte-for-byte identical (case-sensitive). If they look the same but EXACT() returns FALSE, there's a difference — hidden characters or casing.
See exactly what's wrong with your data — try Sohovi free — try Sohovi free.
Test 3: SUBSTITUTE test
=SUBSTITUTE(A2, " ", "|")
Replaces all spaces with | so you can see them. "Acme Corp " becomes "Acme|Corp|" — the trailing space revealed.
The Characters Causing Problems
1. Trailing/Leading Spaces (most common)
- Character: Char(32)
- Source: Copy-paste from web, CRM exports, manual data entry
- Fix:
=TRIM(A2)
2. Non-Breaking Spaces
- Character: Char(160)
- Source: Copy from HTML/web pages (HTML uses
for non-breaking spaces) - Problem: TRIM() doesn't remove Char(160) — it only removes Char(32) (regular spaces)
- Fix:
=SUBSTITUTE(A2, CHAR(160), "")first, then TRIM
3. Line Breaks
- Characters: Char(10) (Line Feed) and Char(13) (Carriage Return)
- Source: Multi-line data entry, some database exports
- Fix:
=CLEAN(A2)removes all non-printing control characters including line breaks
4. Zero-Width Spaces and Other Unicode Spaces
- Various invisible Unicode characters that neither TRIM nor CLEAN remove
- Fix: Use SUBSTITUTE to remove the specific character; see Fix 3 below
The Fixes
Fix 1: TRIM for Trailing/Leading Spaces
=TRIM(A2)
Removes spaces from the start and end, and collapses multiple consecutive spaces to one.
For VLOOKUP:
=VLOOKUP(TRIM(E2), $A$2:$C$100, 2, FALSE)
Add TRIM to the lookup value AND to the first column of the lookup table if both might have spaces.
Fix 2: CLEAN for Line Breaks and Control Characters
=CLEAN(A2)
Removes all non-printing characters (line breaks, tabs, etc.).
Combined:
=TRIM(CLEAN(A2))
This handles the majority of invisible character problems.
Fix 3: SUBSTITUTE for Non-Breaking Spaces
=SUBSTITUTE(TRIM(CLEAN(A2)), CHAR(160), " ")
This first removes control characters (CLEAN), then strips spaces (TRIM), then replaces any remaining non-breaking spaces with regular spaces.
Fix 4: Apply Fixes to the Whole Column Permanently
If you have invisible characters throughout a column:
- Create a helper column with
=TRIM(CLEAN(A2)) - Fill it down for the entire column
- Copy the helper column
- Paste Special → Values Only onto the original column
- Delete the helper column
Now the original column contains clean values, and VLOOKUP will work without wrappers.
VLOOKUP Returning Wrong Values (Not #N/A)
A subtler problem: VLOOKUP finds a match and returns a value, but it's the wrong value. This can happen when:
- The lookup table has multiple near-identical keys due to invisible character differences (one with a space, one without) — VLOOKUP matches the wrong one
- Type mismatch: looking up the number 42 against the text "42" — VLOOKUP with FALSE matches on type, so it may find the wrong entry or return #N/A
Sohovi scores your dataset against your own accuracy standards and highlights the columns and rows where values fall outside expected ranges.
Diagnosis: Use EXACT() to confirm the specific record VLOOKUP is matching, not just whether a match exists.
Frequently Asked Questions
Q: TRIM and CLEAN didn't fix my #N/A. What else could it be?
After confirming both values are the same length (LEN check) and EXACT() returns TRUE: check for type mismatch (number vs text). Use =ISNUMBER(A2) and =ISTEXT(A2) to compare types. If one is a number and one is text, use =TEXT(A2, "0") to convert the number to text, or =VALUE(A2) to convert text to number.
Q: Does TRIM work on numbers?
TRIM converts numbers to text when applied to them. =TRIM(42) returns the text "42". If you apply TRIM to a column of numbers, they become text — this is usually a problem. Apply TRIM only to columns that should contain text.
Q: My file comes from a specific system that always adds trailing spaces. Is there a way to auto-clean on import? Power Query (Excel's Get & Transform) lets you add a "Trim" step to any text column as part of the import transformation. Once set up, every refresh automatically trims that column.
Profile your file's text columns for whitespace and invisible character issues — Sohovi's profiler flags common invisible character problems across all columns automatically. Upload your file free and see which columns need cleaning.