Excel throws #N/A on a VLOOKUP against two CSV exports when the values only look identical on screen. One file has the key stored as text, the other as a number, date serial, or text string with extra characters, and VLOOKUP compares the stored value, not the display.
When Excel VLOOKUP returns #N/A on mismatched CSV column formats, the formula is usually exposing an import problem. CSV files do not carry Excel data types. Excel decides how to interpret each column when the file opens. Two rows that appear identical after formatting can still be different underneath.
Why the match fails even when the values look the same
| What you see | File A stores | File B stores | Result |
|---|---|---|---|
| 12345 | Number 12345 | Text "12345" | No match |
| 0012345 | Text "0012345" | Number 12345 | No match |
| 2026-05-01 | Real Excel date serial | Text "2026-05-01" | No match |
| INV-1007 | Text "INV-1007 " | Text "INV-1007" | No match |
| 1,234.00 | Text "1,234.00" | Number 1234 | No match |
This is why formatting a column as Number or Date often changes nothing. Cell formatting changes the display. It does not always change the stored value.
That is the classic VLOOKUP not matching text numbers problem. The lookup is doing an exact comparison, and exact means exact.
Check the first broken row before you edit the whole file
Find one row that should match and does not. Test that row in helper columns before changing the whole sheet.
| Check | Formula | What it tells you |
|---|---|---|
| Data type | =TYPE(A2) and =TYPE(B2) | 1 means number, 2 means text. If one side is 1 and the other is 2, that is the mismatch. |
| Visible length | =LEN(A2) and =LEN(B2) | Different lengths on values that look the same usually means spaces or hidden characters. |
| Real date test | =ISNUMBER(A2) | TRUE means Excel is storing a number or date. FALSE means text. |
| Hidden character test | =A2=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) | FALSE means the cell changes when cleaned. |
| Direct equality | =A2=B2 | FALSE confirms Excel sees two different stored values. |
If these checks show no format difference, the problem is somewhere else: the lookup key is wrong, the lookup range starts in the wrong column, the fourth VLOOKUP argument is missing, or the value is genuinely absent.
Fix text and number columns without destroying IDs
If the column should be numeric, convert it in a helper column. Do not overwrite the raw CSV column first.
| Situation | Helper formula | Use it for |
|---|---|---|
| Plain numeric value stored as text | =VALUE(TRIM(A2)) | Amounts, counts, numeric references |
| Numeric text with commas or currency symbols | =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),",",""),"$","")) | Amount fields imported as text |
| ID where leading zeros matter | Keep both sides as text | Account codes, padded references, SKU-like IDs |
The important split is this: some values should become numbers, and some must stay text. If the key is 000731, converting it to 731 fixes one problem and creates another.
To keep a padded ID matchable, force both sides to text instead of converting either to a number. If one file stores 000731 as text and the other stores 731 as a number, give both the same fixed-width mask:
=TEXT(A2,"000000")Match on the result. The mask width must equal the real reference length, or you pad one side longer than the other and rebuild the mismatch. If the IDs are not a fixed width, do not pad. Import both columns as text instead, before Excel drops the zeros.
Data > Text to Columns > Finish is a fast repair when an entire numeric or date column imported badly. It forces Excel to parse the values again. Use it on amounts and dates. Do not use it on IDs that depend on leading zeros.
Remove spaces and invisible characters when only some rows fail
A trailing space is enough to make VLOOKUP return #N/A. So is a non-breaking space copied from a web export, a tab, or a line break inside the cell.
| Hidden problem | What you notice | Fix |
|---|---|---|
| Trailing or leading space | The values look the same, but LEN() differs | TRIM() |
| Non-breaking space | TRIM() does not fix it | SUBSTITUTE(A2,CHAR(160)," ") |
| Tabs or line breaks | Random rows fail after copy or import | CLEAN() |
Confirm which character you have first. The fix depends on it. =CODE(RIGHT(A2,1)) returns 32 for a normal space, 160 for a non-breaking space, and 9 for a tab. A normal space yields to TRIM(). CHAR(160) does not, because TRIM() removes only the standard space. Non-breaking spaces come from web pages and browser-based exports, and they survive every TRIM() until you substitute them out.
For CSV work, the safest helper formula is:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))Run that on both files, then point the lookup at the cleaned columns instead of the raw export. If it still fails, the character is not trailing. Walk =CODE(MID(A2,n,1)) across the value to find a non-breaking space or tab sitting inside it. INV then CHAR(160) then 1007 never matches INV-1007, however many trailing spaces you strip. If the failures cluster around one source file, why trailing spaces break CSV matching goes deeper on where those invisible characters come from.
Convert both date columns to one matching key
Date columns are where CSV format mismatch in Excel hides best. One file opens 2026-05-01 as text. The other opens 05/01/2026 as a real Excel date. After you format both cells to look alike, they can still fail to match because one is text and the other is a serial number.
| Raw value | Likely stored as | Safe helper key |
|---|---|---|
| 2026-05-01 | Text from ISO-style CSV | =TEXT(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),"yyyy-mm-dd") |
| 5/1/2026 | Real Excel date | =TEXT(A2,"yyyy-mm-dd") |
| 01/05/2026 | Ambiguous text date | Split day, month, and year explicitly before rebuilding the date |
For a dd/mm/yyyy text date, use:
=TEXT(DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)),"yyyy-mm-dd")If the source is mm/dd/yyyy, swap the day and month positions in that formula. The goal is not prettier formatting. The goal is to make both files produce the same stored key.
Prove the fix on one real row before you normalize the whole file
A full-sheet repair is safer when you can show one broken row turning into one matched row for a specific reason.
Suppose file A contains:
| Raw key | What Excel stored |
|---|---|
001245 | Text with leading zeros |
2026-05-01 | Text date |
1,234.00 | Text amount |
And file B contains:
| Raw key | What Excel stored |
|---|---|
1245 | Number |
5/1/2026 | Real Excel date |
1234 | Number |
If you compare those raw values directly, every lookup can fail for a different reason:
- the ID fails because one side preserved leading zeros and the other
did not
- the date fails because one side is text and the other is a date serial
- the amount fails because one side is stored as text with punctuation
The repair is not one formula copied blindly across every column. It is three different normalization decisions:
| Field type | Safe normalized key |
|---|---|
| ID that must preserve zeros | Clean text on both sides |
| Date | One rebuilt yyyy-mm-dd text key on both sides |
| Amount | True number on both sides |
Once you prove that one row matches after the right normalization, then copy that fix down the full column. That order matters. It stops you from "fixing" 5,000 rows with the wrong coercion rule.
The repair that creates a second mismatch
The most common bad repair is converting everything to numbers because the first failed row looked numeric.
That causes a second failure in the cases where the key is only numeric-looking:
| Looks numeric | What it really is | What the bad repair does |
|---|---|---|
000731 | Padded identifier | Drops the leading zeros |
001245889917744 | Long reference | Risks precision loss if Excel stores it as a number |
2026-05-01 | Text date key | Changes the value into a date serial on only one side |
The inverse mistake happens too: someone wraps every lookup key in TEXT() and accidentally converts amount comparisons into text-string comparisons.
The right question is not "how do I make both columns look the same?" The right question is "what type should this field be for matching?"
Use this split:
- IDs and references: usually text
- Dates: usually one rebuilt comparison key
- Amounts: usually numbers
If one fix solves the first broken row but creates a new mismatch in the next category of rows, you have not normalized the file. You have only moved the error.
Run VLOOKUP against normalized columns, not the raw export
Once each side has a cleaned helper key, build the lookup on that key.
| Match key type | What both helper columns should end up as |
|---|---|
| Numeric code with no leading zeros | Number |
| Reference with letters or leading zeros | Cleaned text |
| Date | The same normalized date key, usually yyyy-mm-dd |
A basic pattern looks like this:
=VLOOKUP(E2,$J$2:$K$5000,2,FALSE)In that example, E2 is the normalized key from file one, and column J in the lookup table is the normalized key from file two. The FALSE matters. Without it, Excel may try approximate matching, which is a different problem.
If the two exports also use different structures and headers, the bigger issue is no longer one VLOOKUP. It is the file comparison itself. In that case, how to compare two bank statement CSV files without formulas is the better workflow.
When this keeps turning into the same monthly repair
What looks like an excel reconciliation formula error is often the same import problem repeating on new exports. One month the dates open as text. The next month the transaction IDs lose leading zeros. The formula keeps taking the blame because that is where the error appears.
