VLOOKUP fails on values that look identical

A lookup or a join returns nothing for rows that plainly should match. Copying both values into a cell and comparing them returns false, even though they look the same on screen.

Why this happens

  • Trailing and leading spaces are invisible and are preserved exactly by anything comparing strings. “ACME ” and “ACME” are different values and always will be.
  • The non-breaking space is the version of this that survives cleanup. It comes from text pasted out of a web page or a word processor, it looks exactly like an ordinary space, and it is not found by a find-and-replace for one.
  • Data entered by hand accumulates this steadily, because a trailing space costs nothing to type and nothing on screen shows it. Exports from systems with fixed-width fields sometimes pad every value in a column the same way.
  • 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 right now

  • Trim both sides of the comparison rather than trying to find the bad rows. It is faster and it catches the ones you would have missed.
  • Check the length of the two values when a trim does not fix it. A difference of one, with no visible difference, is almost always a non-breaking space.
  • Handle the non-breaking space explicitly, by its character code. It is the single most common cause of a comparison that fails against all visible evidence.

Stopping it on the next file

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. On number fields the same pass removes the non-breaking and narrow no-break spaces that European locales use as thousands separators, which is the version of this problem that turns a price column into text.

The vendor price list template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

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. Loading it as-is produces totals roughly double the truth.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. Anything that tries to use them as links works for some rows and not others.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. Some rows look shifted by months. Saving from Excel has now written the new reading back to disk.My SKUs turned into scientific notationA column of product codes, barcodes, or order numbers now reads 1.24E+11. Widening the column does not help, and formatting it as a number gives you a different set of digits than you started with.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes