Why VLOOKUP Cannot Find a Match That Exists
You can see the value in the table, but VLOOKUP returns #N/A. In almost every case the two values are not actually identical — one is text and the other is a number, or one has a space you cannot see.
Test it in an empty cell: =A2=D2, comparing your lookup value with the one in the table. If that returns FALSE while they look the same, you have found the problem — and the cause is nearly always type or whitespace.
That equals test is the fastest diagnostic in Excel. It settles in one keystroke whether you are chasing a data problem or a formula problem, and those need completely different fixes.
Cause 1: numbers stored as text
The most common by a wide margin. To Excel, the number 1234 and the text "1234" are different values, so a lookup never matches — even though both display identically.
This happens constantly with IDs, order numbers, postcodes and product codes, because exports preserve leading zeros by storing them as text.
How to spot it: numbers align right, text aligns left. A small green triangle in the corner of the cell, and a warning that reads "Number Stored as Text", is Excel telling you directly.
Fixes:
- Convert the text to numbers. Select the column, click the warning triangle, choose Convert to Number. Or select the column and use Data → Text to Columns → Finish.
- Convert inside the formula, if you cannot change the source. To look up a number using a text value:
=VLOOKUP(VALUE(A2), D:E, 2, FALSE). The other way round:=VLOOKUP(TEXT(A2,"0"), D:E, 2, FALSE).
Which direction you need depends on which side is text — the equals test tells you nothing about that, so check the alignment on both.
Cause 2: spaces you cannot see
"Smith " and "Smith" are different strings. Trailing spaces are invisible and survive every export.
=TRIM(A2) removes leading, trailing and repeated internal spaces. But if TRIM does not fix it, the character is not a normal space:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Character 160 is a non-breaking space, standard in HTML and identical to the eye. It arrives with anything copied from a web page or a PDF, and TRIM ignores it entirely because TRIM only handles character 32.
To confirm before fixing, compare lengths: =LEN(A2) against =LEN(D2). Different numbers on values that look the same is proof of hidden characters.
Cause 3: the range_lookup argument was left out
The single most damaging mistake in VLOOKUP, because it fails quietly.
The fourth argument controls match type, and Microsoft's documentation is explicit that omitting it defaults to TRUE — approximate match. Approximate match assumes the first column is sorted ascending; on unsorted data it returns whatever it lands on, which is frequently a wrong answer rather than an error.
Always pass FALSE unless you specifically want a banded lookup like tax brackets:
=VLOOKUP(A2, D:E, 2, FALSE)
If your lookup returns wrong values rather than #N/A, this is the first thing to check.
Cause 4: the lookup value is not in the first column
VLOOKUP only searches the leftmost column of the range you give it. If your IDs are in column E and the range starts at column D, it searches D and never finds them.
Either start the range at the column containing the lookup values, or switch to a function without the restriction. XLOOKUP takes the lookup array and return array separately, so the order does not matter:
=XLOOKUP(A2, E:E, D:D)
Where XLOOKUP is unavailable, INDEX/MATCH does the same job: =INDEX(D:D, MATCH(A2, E:E, 0)).
Cause 5: the range shifts when you drag
If the first row works and later rows return #N/A, the table range is moving as you fill down. D2:E100 becomes D3:E101, and rows fall off the bottom.
Lock it with absolute references: =VLOOKUP(A2, $D$2:$E$100, 2, FALSE). Pressing F4 with the range selected in the formula bar adds the dollar signs. Whole-column references like D:E avoid the issue too, though they are slower on large sheets.
Cause 6: hidden characters from an export
Line breaks and control characters survive exports and are completely invisible in the cell.
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
CLEAN strips non-printable characters, SUBSTITUTE handles non-breaking spaces, TRIM removes the leftover padding. That combination handles essentially every dirty-data case in one formula.
Diagnosis order
| Test | Result | Cause |
|---|---|---|
=A2=D2 | FALSE, values look identical | Type mismatch or hidden characters — continue |
| Check alignment | One right, one left | Number stored as text |
=LEN(A2) vs =LEN(D2) | Different lengths | Spaces or hidden characters |
=A2=D2 | TRUE, but still #N/A | Formula problem, not data |
| Count formula arguments | Only three | Missing FALSE — approximate match |
| Check the range's first column | Lookup values not in it | Use XLOOKUP or INDEX/MATCH |
| First row works, rest fail | Range shifts on fill | Add $ absolute references |
Whitespace is one of the two usual causes above. How to remove spaces in Excel covers the formulas that clean it, including the non-breaking spaces TRIM cannot touch.
Frequently asked questions
Why does VLOOKUP say #N/A when the value clearly exists?
Because the two values are not identical to Excel, even though they look identical to you. Test with =A2=D2; if it returns FALSE, the usual causes are one value being text and the other a number, or a trailing or non-breaking space.
Why does VLOOKUP not work with numbers?
Typically because one side is a number and the other is text that looks like a number — common with IDs and codes from exports. Numbers align right and text aligns left. Convert with Text to Columns, or wrap the lookup value in VALUE() or TEXT().
What does the fourth VLOOKUP argument do?
It sets the match type. FALSE means exact; TRUE means approximate and requires the first column sorted ascending. Omitting it defaults to TRUE, which on unsorted data returns wrong values rather than errors. Pass FALSE unless you want a banded lookup.
Why does my VLOOKUP work on the first row but not the rest?
The table range is shifting as you fill down. Lock it with absolute references — $D$2:$E$100 — by pressing F4 with the range selected in the formula bar.
Can VLOOKUP look to the left?
No. It only searches the leftmost column of the range supplied. Use XLOOKUP, which takes lookup and return arrays separately, or =INDEX(D:D, MATCH(A2, E:E, 0)).
Why doesn't TRIM fix my VLOOKUP?
TRIM only removes standard spaces (character 32). Web and PDF sources often contain non-breaking spaces (character 160), which look the same. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), adding CLEAN() if control characters may also be present.
Is VLOOKUP case-sensitive?
No — "SMITH" matches "smith". If case is the only difference you are seeing, that is not the cause of your #N/A.
Conclusion
Run =A2=D2 first. It splits the problem cleanly: FALSE means a data problem (type or whitespace), TRUE means a formula problem (missing FALSE, wrong column, shifting range).
Nearly every "the value is right there" case is a number stored as text or an invisible space. And whatever else you change, pass FALSE as the fourth argument — the default is the one failure here that returns a plausible wrong answer instead of an error.
Related: VLOOKUP and XLOOKUP compared covers when to use each, and Excel formula errors explained covers the other error codes.
Comments