Why VLOOKUP Returns #N/A on Data You Just Imported

Last updated: August 2026 · By the CSVUndo team · 8 min read

The quick answer: when VLOOKUP returns #N/A on values you can plainly see in both tables, the cause is almost always one of three invisible differences the CSV import created: the key is stored as a number on one side and as text on the other; a trailing space is hiding on the end of one value; or that “space” is a non-breaking space — CHAR(160) — which Excel’s TRIM will not remove. The two values look identical on screen, but an exact-match lookup compares character for character and type for type, so Excel truthfully reports that no match exists. The fixes are TRIM, VALUE and SUBSTITUTE(A2,CHAR(160)," ") on one side of the lookup — or cleaning the whole file before it ever reaches Excel.

Why does VLOOKUP fail when the value is clearly in the table?

Because exact-match lookup is stricter than your eyes. With FALSE (or 0) as the fourth argument, VLOOKUP demands that the lookup value and the table value be equal: the same characters, the same length, and the same underlying type. (One documented exception: with FALSE, a ? or * in a text lookup value acts as a wildcard rather than a literal character.) #N/A is not a formula error — it is the function reporting, correctly, that under those strict rules nothing in the first column of your table equals the value you gave it. The maddening part after a CSV import is that the rule is being broken by something you cannot see.

Imports are the classic trigger because every route into Excel makes its own typing decisions. Open a CSV by double-clicking and Excel guesses each column’s type; import it through Power Query and different rules apply; receive the second file from a colleague whose export added padding, and the two “matching” key columns have quietly diverged. Before touching the data, prove the mismatch: pick one pair that should match and type =A2=Sheet2!A2 in a spare cell. FALSE confirms the two values differ invisibly. TRUE means your data is fine and the problem is the formula itself — usually a wrong column index or a range that doesn’t lock with $ signs when filled down.

Once FALSE has confirmed an invisible difference, there are only three usual suspects, and each has a one-formula test:

Is it a text vs number mismatch?

The number 42 and the text "42" are not equal in Excel — ever. They render almost identically in a cell, but to an exact-match lookup they are as different as 42 and banana. This is the single most common reason a lookup fails on freshly imported data: your order export stored the customer IDs as text (often deliberately, to protect leading zeros), while the file you are looking them up against was opened in Excel, which converted the same IDs to numbers. Every key exists in both files; not one of them matches.

The visual tells are worth learning. Text-stored numbers align to the left of the cell by default and usually carry the green-triangle warning in the corner — the one covered in our guide to “number stored as text” in Excel. Real numbers align to the right. A column where half the values sit left and half sit right is a column with mixed types, and mixed types mean a lookup that works on some rows and fails on others — the most confusing failure mode of all.

To confirm it, put =ISNUMBER(A2) next to the lookup value and =ISNUMBER(Sheet2!A2) next to the table key it should hit. If one says TRUE and the other FALSE, you have your culprit, and the fix is to convert exactly one side until they agree:

1. Table stores numbers, lookup value is text: wrap the lookup in VALUE — =VLOOKUP(VALUE(A2),Sheet2!$A$2:$C$500,2,FALSE).
2. Table stores text, lookup value is a number: wrap it in TEXT — =VLOOKUP(TEXT(A2,"0"),Sheet2!$A$2:$C$500,2,FALSE).
3. Convert a whole column in place: select it, then Data → Text to Columns → Finish. Excel re-evaluates every cell’s type in one pass. The old trick of multiplying by 1 via Paste Special → Multiply does the same for text→number.

One warning before you bulk-convert: if those text-stored keys have leading zeros, converting them to numbers deletes the zeros, and now the values themselves disagree. That trap has its own guide — why CSV leading zeros disappear in Excel.

Is an invisible space breaking the match?

If the types agree and it still fails, count characters. A trailing space is the second great lookup-killer: "AC-1042 " and "AC-1042" are different strings, and no amount of squinting at the screen will reveal the difference. The detector is LEN: put =LEN(A2) next to the suspect value and compare it with the length of its supposed twin in the other file. An 8 next to a 7 tells you a character is hiding; =LEN(A2)>LEN(TRIM(A2)) returning TRUE tells you ordinary spaces are involved — leading, trailing, or doubled up between words, since TRIM strips all three.

Then identify which whitespace, because the fix depends on it. Read the last character’s code with =CODE(RIGHT(A2,1)) on Windows, where CODE and CHAR use the ANSI character set. Excel for Mac uses the Macintosh character set for those two functions, so on a Mac read the code with =UNICODE(RIGHT(A2,1)), which is code-page independent:

1. 32 is the ordinary ASCII space. Plain TRIM removes it and your lookup starts working.
2. 160 is the non-breaking space, Unicode U+00A0 — the character web pages write as  . It rides into spreadsheets in anything copied from a browser, a PDF, an HTML table or certain system exports, and Excel’s TRIM does not touch it. If your TRIM “isn’t working”, this is almost certainly why — we cover the character itself in depth in TRIM won’t remove the space: it’s CHAR(160).

The non-breaking space deserves its reputation as the nastiest of the three causes: it defeats the one function everybody reaches for, it survives most eyeballing because it renders exactly like a space, and one single instance on one side of the lookup is enough to fail the match.

Which formula fixes actually work?

Wrap the lookup value in the repair that matches the diagnosis. These four patterns cover the real-world cases; use the narrowest one that fixes your test pair rather than stacking everything defensively:

1. Ordinary trailing/leading spaces: =VLOOKUP(TRIM(A2),Sheet2!$A$2:$C$500,2,FALSE)
2. Non-breaking spaces: =VLOOKUP(TRIM(SUBSTITUTE(A2,CHAR(160)," ")),Sheet2!$A$2:$C$500,2,FALSE) — SUBSTITUTE converts each CHAR(160) to a real space, and TRIM then strips it.
3. Text lookup value, numeric table: =VLOOKUP(VALUE(TRIM(A2)),Sheet2!$A$2:$C$500,2,FALSE)
4. Numeric lookup value, text table: =VLOOKUP(TEXT(A2,"0"),Sheet2!$A$2:$C$500,2,FALSE)

Notice the asymmetry, because it matters: wrapping functions around the lookup value is easy, but if the dirt is in the table’s key column, no wrapper on the lookup side can help — TRIM(A2) cleans your value, not their column. You would need a helper column beside the table (=TRIM(SUBSTITUTE(B2,CHAR(160)," ")), filled down, then Paste Special → Values over the original) — per column, per file, every time the export is re-run. That is exactly the point where fixing the file once beats patching every formula that reads it.

How do I clean the whole file instead of patching formulas?

Repair the CSV once, before import, and every lookup against it works. Drop the file into the CSVUndo CSV Cleaner and tick Trim extra spaces. Unlike Excel’s TRIM, its trimming covers all Unicode whitespace, so a trailing CHAR(160) is removed exactly as an ordinary space is, in every cell, in one pass. The before/after preview shows what will change and the summary counts how many cells were trimmed, so you can confirm the fix did only what you asked. If both of your files were exported dirty — common when the same upstream system produced them — clean both, so the keys agree from either direction.

Two practical notes, honestly stated. First, the cleaner trims whitespace from the ends of cells; it does not change the values inside them, so it will not convert text to numbers — type conversion stays a job for VALUE or Text to Columns after import. Second, the engine runs on your device’s main thread: files over 12 MB get a heads-up that the parse may take a few seconds, and files over 60 MB are refused outright rather than freezing your tab. Within those limits it handles the lookup tables that matter, and because everything runs locally there is no upload step to wait for — or to worry about.

Could the lookup keys themselves be damaged?

Sometimes the values really are different — because Excel rewrote them. If cleaning and type conversion still leave #N/A everywhere, stop debugging the formula and inspect the keys, because a trip through Excel may have changed the data itself.

Excel stores every number with at most 15 significant digits, so a 16-digit ID that passed through Excel as a number has already had its final digit replaced with a zero. If one file went through that and the other did not, 1234567890123456 is being looked up against 1234567890123450 and no formula can bridge them. Worse, if the file was saved that way, the original digits were never written to disk — they are unrecoverable, and the only real fix is re-exporting from the source system with the IDs protected as text. Our guide on Excel zeroing the last digits of long numbers covers the escape routes.

Two related rewrites hit lookup keys constantly. Leading zeros: one file has 00501, the other — opened in Excel once — has 501, and the leading-zeros repair tool can restore them for codes with a standard width, such as 5-digit ZIP codes, 12-digit UPCs and 13-digit EANs. And scientific notation: a key column showing 1.23457E+12 has lost its exact digits on screen, a mess with its own causes and fixes. One caution our own repair tool takes seriously: a value that merely looks like scientific notation is not proof of damage — 2E5 can be a genuine part number — so never bulk-expand a column on shape alone; check what the values are supposed to be first.

Frequently asked questions

Sources: Microsoft’s own troubleshooting page, How to correct a #N/A error in the VLOOKUP function, lists cell-format mismatches and stray spaces among the leading causes. The 15-significant-digit limit is documented in Microsoft’s note on last digits changed to zeros.