VLOOKUP fails on values that look identical
A lookup or a join returns nothing for rows that plainly should match.
Why it happens
- Trailing and leading spaces are invisible and are preserved exactly by anything comparing strings.
- The non-breaking space is the version of this that survives cleanup.
- Data entered by hand accumulates this steadily, because a trailing space costs nothing to type and nothing on screen shows it.
- The failure mode is the annoying kind: not an error, just an empty result, which reads as “this row has no match” rather than as “these two strings differ by a character you cannot see”.
What to do now
- Trim both sides of the comparison rather than trying to find the bad rows.
- Check the length of the two values when a trim does not fix it.
- Handle the non-breaking space explicitly, by its character code.
Next time
Every cell is trimmed before it is typed or compared, so “ ACME ” and “ACME” are one value rather than two: they collide on a unique field, and they merge across deliveries instead of producing a second row for the same supplier.
Start from the vendor price list template.
Related problems
My export has subtotal rows mixed in with the dataThe file has real rows, but also rows that are section headings with one cell filled, subtotal rows with a figure and no product, and a grand total at the bottom.My website column will not work as linksA column of company websites contains a mixture of forms: some with https://, some starting www, some bare domains, and a few entries that are not addresses at all.Excel changed the dates when I opened the CSVYou opened a CSV, and dates that read one way in the file now read another way on screen.My SKUs turned into scientific notationA column of product codes, barcodes, or order numbers now reads 1.24E+11.