VLOOKUP Not Working? Why You Get #N/A — and How to See the Real Reason

You can see the value with your own eyes. It's right there in the other sheet. And VLOOKUP still returns #N/A. The frustrating part isn't that the formula failed — it's that it returned the same three characters no matter why it failed. A trailing space, a number stored as text, a hidden character pasted in from a web page, or a customer who genuinely isn't in the other list all produce one identical, silent answer. This guide covers every real cause, the native fix for each, and the one thing no formula can do for you: tell you which cause you've actually got.

First, rule out the two that aren't really lookup failures

Before hunting invisible characters, check these. They're the most common and the fastest to fix.

You forgot the fourth argument

=VLOOKUP(A2, Sheet2!A:D, 4) is not an exact-match lookup. The fourth argument range_lookup defaults to TRUE — approximate match — which assumes your lookup table is sorted ascending by its first column. On unsorted data it returns confidently wrong answers on some rows and #N/A on others. Always write FALSE: =VLOOKUP(A2, Sheet2!A:D, 4, FALSE). If adding FALSE turns some working rows into #N/A, those rows were never really matching — they were being handed their nearest neighbour.

Your lookup value isn't in the first column of the range

VLOOKUP can only search the leftmost column of table_array. If your IDs live in column C and you passed A:D, it's searching column A and will never find them. Either move the column, or switch to XLOOKUP / INDEX MATCH, which have no such rule. Related: if you dragged the formula down without locking the range ($A$1:$D$500 rather than A1:D500), the range slid down with each row and the bottom of your list is looking at nothing.

The five causes that survive a correct formula

Your formula is right, exact match is on, the range is locked and absolute. It still returns #N/A. Now you're in the real problem: the two IDs are not the same string, even though they look identical. Here is what that actually means, in rough order of how often it's the answer.

Every row above fails against a clean AC-#### list, and every one of them looks fine in the cell. That is the entire problem in one picture.

1. Trailing and leading spaces

"AC-9036 " and "AC-9036" are different strings. Exports from ERP, CRM and accounting systems pad fixed-width fields constantly. The fix: =TRIM(A2) in a helper column, on both sides — trimming one side doesn't help if the padding is on the other. TRIM also collapses runs of internal spaces to one, which is usually what you want and occasionally isn't.

2. Non-breaking spaces and other invisible characters

This is the one that makes people give up, because the obvious fix doesn't work. A value pasted from a web page, a PDF or an email often carries a non-breaking space — CHAR(160), not the ordinary CHAR(32). TRIM does not remove it. Neither does CLEAN, which only strips control characters 0–31 and leaves 160 completely alone. So you run both, nothing changes, and you conclude the data is fine. The fix: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). And that still won't catch a zero-width space, a byte-order mark or a soft hyphen, each of which needs its own SUBSTITUTE and none of which you can see to know you need it.

3. Numbers stored as text (in either direction)

The lookup value is the number 1001 and the table holds the text "1001", or the reverse. To Excel these are different types and will never match. The classic source is a CSV round-trip that turned a real number into a quoted string, or a leading apostrophe ('1001) that's invisible in the cell. The fix: coerce one side — =VLOOKUP(VALUE(A2), …) to make text numeric, or =VLOOKUP(A2&"", …) to make a number textual. Get the direction wrong and you've swapped one #N/A for another. There's a fuller treatment of this in converting numbers stored as text.

Watch the zero-padding trap here. "00123" and "123" are two different account codes in most systems, and "helpfully" converting both to the number 123 to force a match is how you end up posting to the wrong ledger.

4. Capitalisation — but not for VLOOKUP

Worth stating plainly because it's so widely misreported: VLOOKUP, XLOOKUP and MATCH are all case-insensitive. aC-8553 matches AC-8553 quite happily. If you want a case-sensitive lookup in Excel you have to build one with EXACT(). Where capitalisation does bite you is everywhere else: Power Query merges are case-sensitive, as are most database joins and nearly every scripting language. So a mixed-case ID column is a genuine defect that just happens to be invisible while you're using a formula — and becomes an outage the day you move the same join into Power Query.

5. It genuinely isn't there

Sometimes #N/A is correct and the honest answer is "this customer isn't in the reference list." This is the important one, because it's the only cause you shouldn't "fix" — it's information. The whole difficulty is that it's indistinguishable from the other four until you've eliminated them.

Why IFERROR is the worst thing you can do here

The most popular answer on the internet is to wrap the formula: =IFERROR(VLOOKUP(…), ""). It makes the red errors disappear, the sheet looks finished, and you send it.

It has not fixed anything. It has converted a visible problem into an invisible one. The seventeen rows that failed because of a trailing space now look exactly like the rows that legitimately had no match, and both look like rows that matched to a blank. Every downstream SUM, pivot and filter now quietly under-reports, and there is no longer any signal that it's doing so. IFERROR is the right tool once you know the remaining misses are genuine. Reaching for it first is how a reconciliation ships wrong.

The native diagnostic pass, and why it's so slow

The honest way to do this in Excel is a helper-column funnel. Add a column with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")), look up against that, and count what's left. Then add VALUE() and count again. Then UPPER() and count again. The drop at each stage tells you how many rows that cause was responsible for.

It works. It also means four or five passes, a spreadsheet full of scaffolding to unpick afterwards, and a method that tells you how many rows each cause hit but never which ones. And you have to already know the list of causes to test for — which is precisely the knowledge you don't have when you're staring at your first #N/A.

See the reason instead of guessing it

This is the job Bring Columns Over exists to do. Drop your file, point it at the second list, and before it changes a single cell it shows you the whole answer: how many rows will match, and a reason for every row that won't.

Every miss gets exactly one reason

That arithmetic is deliberate and it's enforced in the engine: every unmatched row is attributed to one cause, so the reason counts always sum to the miss total. You can check the screen against your own file. A report where the numbers don't add up is worse than no report, because the one thing it has to be is believable.

The fixes are offered, not applied

Each reason carries its own one-tap repair — Trim them, Ignore capitalisation — and tapping one re-runs the entire match rather than patching the number on screen, so the count you end up looking at is true by construction. Nothing is enabled on your behalf to make the match rate look better.

It never edits your data to make itself look good

This is the invariant that matters most. Turning on Ignore capitalisation or Trim them changes how the two lists are compared — it does not write back to your key column. Your IDs stay exactly as they arrived. (If you also want them genuinely cleaned, that's a separate, explicit choice.) Quietly normalising someone's identifiers to improve a statistic is the sort of thing that's discovered six months later.

Near misses are shown, never assumed

Below the reasons sits a list of IDs that are one or two characters from a real one. They are ticked one at a time, and unticking hands the row straight back. If a key is equally close to two different candidates, it names both and refuses to bind either — an ambiguous match applied silently is the failure mode that makes a whole reconciliation untrustworthy. And on a densely numbered list, where consecutive SKUs are all one character apart from each other by definition, they simply aren't offered.

Duplicate keys are surfaced, not silently resolved

Here is the one VLOOKUP never mentions. If your reference list contains the same ID twice with different values, VLOOKUP takes the first one it finds and says nothing at all. No error, no warning — a wrong number ships and nobody questions it, because the formula looked like it worked. The Match Report stops and tells you the key is duplicated and the rows disagree, rather than guessing on your behalf.

An unmatched row gets a blank, never #N/A

When you do apply it, rows that didn't match get an empty cell. Not #N/A — an error value poisons every downstream SUM, sort and pivot in the workbook. And the row itself is never dropped: you can send unmatched rows to their own sheet, each carrying the reason it didn't match, so nothing vanishes silently.

What it deliberately doesn't do

• It never hands you a formula. There's no "copy this VLOOKUP" button, on purpose. A formula in the cell is the thing that couldn't explain itself in the first place.
• It won't match blank to blank. Two empty keys joining each other is the most destructive plausible-looking result a tool like this could produce, so blank and whitespace-only keys are excluded from both sides and counted as their own reason.
• It won't merge two IDs that differ only in digits. AC-1004 and AC-1204 are two different accounts, not a typo, and no amount of similarity scoring changes that.
• It doesn't upload your file. The match engine has no network surface at all — key detection, the join, the near-miss pass and the download all run in your browser. See how TidyCell works.

The whole report above — the count, every reason, the near misses, the duplicate warning — is free and needs no signup. Applying the result to your workbook is a Pro feature.

Common questions

Does XLOOKUP fix this?

It fixes the formula problems — it defaults to exact match, searches any column, and takes an if_not_found argument. It does not fix the data problems. A trailing space, a non-breaking space and a text-versus-number mismatch break XLOOKUP exactly as they break VLOOKUP, and if_not_found is IFERROR with better manners: it still hides the reason.

Why does my lookup work for some rows and not others?

Almost always because the problem is per-value rather than per-formula. Twelve of your IDs were pasted from an email and carry a non-breaking space; the rest were typed. The formula is fine — it's correct about every row, including the ones you think it's wrong about.

I only want to know what's different between two lists — not merge them.

Then you want Compare Two Lists instead: in both, only here, only there, or in both but the numbers disagree — with the difference worked out and sorted by size.

Can it match on more than one column?

Yes. When no single column is unique enough on its own — first name plus last name, order number plus line number — it will propose a second key column rather than pretending the first one was sufficient.

My reference list is in a completely separate file.

That's fine, and it doesn't get uploaded either. You can point at another sheet in the same workbook or attach a second file; both stay on your computer.