Excel data cleaning
VLOOKUP Not Working in Excel? Fix #N/A, Hidden Spaces and Text Numbers
When VLOOKUP returns #N/A for a value you can see, the two values are almost never identical underneath. The usual causes are a stray space (including the non-breaking kind), a number stored as text on one side only, and a missing FALSE at the end of the formula. Test with =A2=E2 on the two cells: if it says FALSE, they differ, and the steps below show how.
VLOOKUP is exact about what it compares. "1001" as text and 1001 as a number are different things to it, and so are "Smith" and "Smith ". Because they look the same on screen, the failure feels random. It is not: one of the five causes below is almost always behind it.
Step 1: check the two cells really match
Put the lookup value in one cell and the matching value from the table in another, then compare them directly:
=A2=E2TRUE means they match and the problem is in the formula (see cause 5). FALSE means the data differs even though it looks the same. Next, compare the lengths:
=LEN(A2) =LEN(E2)If the lengths differ, there are hidden characters. If they are equal but the comparison is still FALSE, one side is probably a number and the other text.
Cause 1: trailing and leading spaces
A space at the end of "Smith " is invisible. Remove it from the lookup column and from the table:
=TRIM(A2)Fill down, copy, and Paste Special, Values, over the original. Do this on both sides, because the stray space can be in either place. The guide on removing extra spaces in Excel covers every variant.
Cause 2: non-breaking spaces
Data copied from a web page or PDF often contains character 160, which looks like a space but which TRIM does not touch. If TRIM changed nothing and the lengths still differ:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Cause 3: numbers stored as text
If one column holds the number 1001 and the other the text "1001", VLOOKUP will not match them. Signs: a green triangle in the corner of the cell, numbers aligned to the left, or =ISNUMBER(A2) returning FALSE. To convert text to numbers:
=VALUE(TRIM(A2))Or the other way round, to turn the numbers into text so both sides are text: =A2&"". Pick one type and make both columns the same. The full method is in converting text to numbers in Excel.
Warning: do not convert IDs, phone numbers or postcodes to numbers just to make a lookup work. Leading zeros would be lost. Convert the other side to text instead.
Cause 4: the lookup value is not in the first column
VLOOKUP only searches the first column of the table you give it and returns a value to the right. If the value you search for sits in the second column, the result is #N/A. Either move the search column to the left, or use INDEX and MATCH, or XLOOKUP if your Excel has it, which can look in any column.
Cause 5: the formula itself
Check the last argument. Without it, VLOOKUP assumes an approximate match, which only works on sorted data and returns wrong or missing results otherwise. For an exact match, always end with FALSE:
=VLOOKUP(A2,$E$2:$G$500,2,FALSE)Also lock the table range with dollar signs. If the range slides down as you fill the formula, the bottom rows silently search a smaller and smaller table.
A quick checklist
| Symptom | Likely cause | Fix |
|---|---|---|
| Looks identical, comparison is FALSE | Hidden space | TRIM, then SUBSTITUTE with CHAR(160) |
| Green triangle, left-aligned numbers | Number stored as text | VALUE, or make both sides text |
| Works for the first rows, fails further down | Table range not locked | Use $E$2:$G$500 |
| Wrong results, not #N/A | Approximate match | Add FALSE as the last argument |
| Always #N/A for one column | Search column is not the first | INDEX and MATCH, or XLOOKUP |
Clean both lists before you look anything up
Most VLOOKUP trouble is dirty data, not a formula mistake. DecodingPro trims every text cell (including non-breaking spaces), turns text that is really a number into a real number, and logs every change, so two lists that should match actually do. Run both lists through first and the lookup usually works without touching the formula.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Why does VLOOKUP return #N/A when the value exists?
The two values are not identical underneath. The most common reasons are a trailing or non-breaking space, a number stored as text on one side, or a missing FALSE for exact match. Compare the cells with =A2=E2 to confirm.
Does TRIM fix every hidden space?
No. TRIM removes the ordinary space only. Spaces copied from web pages are often character 160, which needs SUBSTITUTE(A2,CHAR(160)," ") before TRIM.
Should I use XLOOKUP instead of VLOOKUP?
If your version of Excel has XLOOKUP, it is easier: it defaults to exact match and can search any column. It still fails on hidden spaces and text numbers, so cleaning the data comes first either way.
How do I stop #N/A from showing in my results?
Wrap the formula in IFERROR, for example =IFERROR(VLOOKUP(A2,$E$2:$G$500,2,FALSE),"not found"). Do this after fixing the data, because IFERROR also hides real problems you would want to see.