VLOOKUP or XLOOKUP not matching in Google Sheets™: spaces, zeros and the sorted trap
The value is right there in the other table, and the lookup says #N/A. Or worse,
it returns a number from the wrong row. In Google Sheets™ it is almost always one of
five things. Here is how to tell which, and how to fix each.
First, find out what is actually in the cell
Put these beside a key that should match and does not:
=LEN(A2): longer than it looks means hidden characters.=ISNUMBER(A2): TRUE on one side and FALSE on the other means a number is being compared with text.=CODE(RIGHT(A2)):32is a trailing space,160a non-breaking space.=EXACT(A2, Other!A2): compares two cells you think are equal, character for character.
1. Trailing and leading spaces
INV12 and INV12 are different text. Exports from accounting
and banking systems pad fields all the time. Wrap the key in TRIM on both sides,
or add a helper column that does: =TRIM(A2).
2. Non-breaking spaces
Text copied from a web page or a PDF often carries CHAR(160), which looks like a
space and does not count as one. Replace it first, then trim:
=TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
3. Numbers stored as text, and leading zeros
One system exports the reference 0012 as text; the other has the number
12. A lookup will not match the two. Decide which form is right for the column
and convert the other: =TO_TEXT(A2) for text, =VALUE(A2) for
numbers. If the zeros are only padding, strip them from the text side too, so
INV-0012 and INV12 meet:
=REGEXREPLACE(REGEXREPLACE(LOWER(TRIM(TO_TEXT(A2))), "[^a-z0-9]", ""), "(^|[^0-9])0+([0-9])", "$1$2")Watch for long references stored as numbers. A spreadsheet keeps 15 significant digits, so a 16-digit card or account reference has already lost its last digit. No formula can bring it back. Re-import that column as text.
4. VLOOKUP's sorted default
In Google Sheets™, VLOOKUP's fourth argument, is_sorted, defaults to
TRUE. Leave it out and the lookup assumes the first column is sorted and
returns the nearest match below the key, a number from the wrong row with no
error at all. Always write the FALSE:
=VLOOKUP(A2, Bank!A:D, 3, FALSE)XLOOKUP matches exactly by default, which is one reason to prefer it.
5. The key is somewhere else in the row
VLOOKUP only searches the first column of its range and only returns columns to
the right of it. If the bank export puts the reference in column C, either start the range at
C or use XLOOKUP(A2, Bank!C:C, Bank!B:B), which does not care about column order.
When it is a whole reconciliation, not one lookup
If you are fixing lookups to compare two whole tables, such as a ledger against a bank export or a statement against a purchase ledger, that is a reconciliation, and there are more ways for it to go quietly wrong. Keys can appear twice, rows can be missing from either side, and the totals need explaining. Two-File Reconciliation is a Google Sheets™ add-on that matches two tables on a key with the space, case, punctuation and leading-zero clean-up built in. It reports what agrees, what differs and what is on one side only, and explains the difference in the totals. It never touches your source tabs. Free up to 1,000 rows a side. The manual method, step by step, is here too.
Install: in Google Sheets™, Extensions › Add-ons › Get add-ons, search for “Two-File Reconciliation”.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Microsoft and Excel are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Google or Microsoft.