Here is the moment stage four gets real. Two tables that
obviously describe the same things —
orders here, customers there — and your
XLOOKUP returns
#N/A for rows you can see have partners. "Byrne Ltd" sits
in both tables. Excel insists it doesn’t.
Excel is right. To a lookup, a key — the value used to match —
either matches exactly or not at all. "Byrne Ltd " (trailing
space), "BYRNE LTD", "Byrne Ltd." and "Byrne Limited" are
four strangers. Humans read intent; lookups read characters. The
craft of joining real-world tables is closing that gap — and it
has an escalation ladder.
Rung one: hygiene (this fixes most of it)
Before anything clever, run the cleaning routine on both key columns — not just one:
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
Spaces you can’t see are the number-one cause of failed matches;
text-numbers
are number two ("1042" in one table, 1042 in the other —
same digits, different kinds; check both columns’ alignment).
Case, at least, is free — Excel’s lookups ignore it.
The diagnostic that earns its keep forever:
=LEN(A2)
"Byrne Ltd" should be 9 characters. If LEN says 10, you’ve
found the invisible passenger. Two LEN columns side by side
settle “why won’t these match” in ten seconds.
Rung two: standardise into a helper key
If the differences are systematic — punctuation, suffixes, formatting — don’t fight them row by row. Build a key column in both tables that boils each name down to its essence, then look up on that:
=LOWER(SUBSTITUTE(SUBSTITUTE(TRIM([@Name]), ".", ""), " ltd", ""))
Chain text surgery
to strip what varies: dots, Ltd/Limited, double spaces. The
same recipe on both sides turns four strangers back into one
company. This is honest work, it’s auditable — the helper column
shows what matched — and it’s the pattern behind every
“master data” fix you’ll ever do.
Rung three: wildcards, for contained keys
When one table’s key lives inside the other’s text (“find the
order whose reference contains this code”), lookups accept
wildcards — * for anything, ? for one character:
=XLOOKUP("*" & [@Code] & "*", Refs[Reference], Refs[Order], , 2)
(The 2 switches XLOOKUP into wildcard mode.) Powerful, and to
be used with the respect owed to anything that can match more
than you meant — a short code like "10" is contained in
hundreds of references. Wildcards are for keys long enough to be
unambiguous.
Rung four: know when you’ve left formula country
True fuzzy matching — "Jon Smyth" vs "John Smith", no rule
in common — is not a formula problem. Pretending it is produces
the worst spreadsheets in the world. The honest options:
Power Query has a
real fuzzy merge (similarity thresholds, ignore-case, a
transformation table for known aliases — and it’s repeatable
every month); or fix the source so both systems share a real ID.
Every messy-key problem is ultimately a missing-ID problem —
customer numbers exist so names never have to match.
Which points at the lasting rule: match on IDs when they exist; build a key column when they don’t; reach for fuzzy tools only when no rule can be written — and then check their output, row by sampled row, like the duplicates you counted before deleting.
The lookup was never broken. The keys were lying — and now you have the whole ladder for making them tell the truth.