Why VLOOKUP doesn't match values that look the same
The two cells look identical and the formula says #N/A. Something invisible is different, and there are only about eight things it can be. Which one, how to prove it, and what to do when the problem is not the formatting but the lists.
Because the two values are not the same, and the difference is something the cell does not show. Put =LEN(A2) beside each of them: if the lengths differ, one has a space or an invisible character in it. If the lengths agree, one is a number and the other is text that looks like a number, which =ISNUMBER(A2) settles. Those two checks find nearly every case. The rest are a hyphen against a dash, a letter O against a zero, a code that lost its leading zeros when it became a number, or a formula with the wrong fourth argument. And sometimes the lookup is working perfectly and the lists genuinely disagree, which is a different job.
What to check, in order
| The invisible difference | How it got there | How to prove it | The fix |
|---|---|---|---|
| A trailing or leading space | Pasted from a web page, a PDF or another system | LEN() of the two cells differ by one or two | TRIM() on the lookup value, or on the whole column |
| A non-breaking space (character 160) | Copied from a browser; TRIM() will not remove it | LEN() differs and TRIM() does not help | SUBSTITUTE(A2,CHAR(160),""), then TRIM() |
| A number stored as text | Imported from a CSV, or typed with an apostrophe, or the column was formatted as text | ISNUMBER() is TRUE on one side and FALSE on the other; text sits left in its cell | VALUE() on the text side, or --A2, or Text to Columns with Finish |
| A code that lost its leading zeros | 00123 became 123 when the file was opened, because the column was read as numbers | The lengths differ and one side has no zeros at the front | TEXT(A2,"00000") on the number side, or import the column as text |
| A hyphen against a dash | Word and the web turn a hyphen into an en dash between numbers | CODE(MID(A2,n,1)) gives 45 for a hyphen and 8211 for the dash | SUBSTITUTE() the dash for a hyphen on one side |
| The letter O for a zero, or l for 1 | Typed by a person, or read by OCR | The lengths agree and both are text; look at the characters | There is no formula for this. Find them and correct them. |
| Wrong fourth argument | VLOOKUP(x, range, n) without FALSE does an approximate match on a sorted list | The formula returns a value, and it is the wrong row's | Add FALSE, or use XLOOKUP, which matches exactly by default |
| The lookup column is not the first column | A column was inserted, or the range starts in the wrong place | #N/A on every row, not just some | Start the range at the column being looked up; INDEX/MATCH or XLOOKUP do not have the restriction |
Case is not on the list, because VLOOKUP ignores it: inv-1001 finds INV-1001. If two values differ only in case and the formula still misses, it is one of the rows above, not the case.
The two-cell diagnosis
Take one value that should match and does not, put it and its twin from the other list side by side, and run three formulas against each: =LEN(), =ISNUMBER() and =CODE(RIGHT()). Different lengths is a space or an invisible character, and the last-character code tells you which: 32 is a space, 160 a non-breaking one, 10 or 13 a line break that came along with a paste. Same length, different ISNUMBER, is text against number. Same length, both text, and a difference you still cannot see is a substituted character — a dash, a letter O — and CODE(MID()) on each position finds it. This takes two minutes and it is faster than any amount of reformatting on the off chance.
When the formula is fine and the lists are not
Sometimes there is nothing invisible at all. The bank statement says 980.05 and the ledger says 980.50, because two digits were swapped on entry. One payment of 1,775 covers two invoices of 1,450 and 325, so neither invoice number is on the statement. A payment arrived for an invoice that is not on the list at all. A lookup formula handles every one of those the same way: #N/A, or worse, a silent pairing of the wrong rows by whichever coincidence came first. The formula is doing its job, which is exact matching, and the job you actually have is reconciliation, which is finding out why two lists that should agree do not.
RECONCILE is built for that job rather than the formula's. It takes both lists and matches in passes, hardest evidence first: the exact reference and amount; then the same reference once punctuation and case are ignored, which is where INV-1001, inv 1001 and INV1001 become one invoice and a trailing space stops mattering; then the reference alone, which catches the swapped digits as a pair that is out by 0.45; then the amount alone; and then combinations, where one payment is found to be the sum of two or three invoices. What is left is the exceptions list, which is the output: a short list of rows a person has to look at, each with the reason it is there.
Run on its own example — five invoices against five payments — it pairs three, finds that INV-1002 was paid 980.05 against 980.50, that the payment marked INV-1003+1005 is INV-1003 plus INV-1005 to the penny, and that INV-1006 was paid and is not on the list. Run on the lists in the table above — a trailing space, an apostrophe, an en dash, a letter O — it pairs the first three through the punctuation-and-case pass and pairs 10025 with 1OO25 on the amount alone, and lists that pairing as one to look at, with both references, because a match found by amount and nothing else is a coincidence until somebody has checked it.
What it does not do
It does not fix a spreadsheet; it reads two lists as CSV or pasted text, in the browser, and neither leaves the machine, which for a bank statement is not a small point. It does not decide that 980.05 and 980.50 are the same payment; it says they are the same reference and different amounts, and leaves the decision where it belongs. And combinations are capped at three rows, because a sum found among thousands of subsets is a coincidence rather than a finding.
Questions people ask about Why VLOOKUP doesn't match values that look the same
The values look identical. What is different?
Something the cell does not show. Put =LEN() beside both: different lengths means a space or an invisible character, and =CODE(RIGHT()) says which. Same length, then =ISNUMBER(): one being a number and the other text that looks like a number is the commonest cause of all. Same length and both text, and it is a substituted character — an en dash for a hyphen, a letter O for a zero.
TRIM did not fix it. Why?
Because the space is a non-breaking one, character 160, which comes along with anything copied from a browser and which TRIM() leaves alone. SUBSTITUTE(A2,CHAR(160),"") first, then TRIM().
Does VLOOKUP care about upper and lower case?
No. inv-1001 finds INV-1001. If two values differ only in case and the lookup still misses, the difference is something else — a space, a dash, text against number — and case is a red herring.
Why does the formula return a value from the wrong row?
Almost always because the fourth argument was left off. VLOOKUP(x, range, n) without FALSE does an approximate match, which on an unsorted list returns whatever it lands on. Add FALSE, or use XLOOKUP, which matches exactly unless told otherwise.
The formula is right and the lists still do not agree. Now what?
Then it is not a lookup problem; it is a reconciliation, which is finding out why two lists that should agree do not: swapped digits, one payment covering three invoices, a payment for something not on the list. A formula gives #N/A for all of those. RECONCILE matches in passes, hardest evidence first, and hands back the short list of rows a person has to look at, each with the reason.
What does 'matched on the amount only' mean?
That two rows have the same amount and references that agree with nothing, even once punctuation and case are ignored — 10025 against 1OO25 with the letter O. It is counted as a pairing and it is listed as an exception with both references, because a match found by amount and nothing else is a coincidence until somebody has looked at it.