How to match names that are spelled differently across sheets

The problem: same entity, different strings

Supplier lists, CRM exports and invoice systems rarely spell names the same way. “ACME Ltd.”, “Acme Limited” and “acme ltd” are one company; “Smith & Sons” and “Smith and Sons” too. An exact lookup finds none of these. Power Query has a built-in fuzzy merge, but it isn't available everywhere and its similarity threshold is a black box for many users. The approach below works in Excel, Google Sheets or SQL, and keeps every decision visible.

Step 1: normalise into a match key

Most mismatches disappear with deterministic cleaning, which is safer than any fuzzy score:

1. Lower-case and trim.
2. Replace “&” with “and”; remove punctuation (full stops, commas, brackets, apostrophes).
3. Remove legal and filler words: ltd, limited, plc, llp, inc, co, uk, the, group.
4. Collapse repeated spaces. Optionally remove all spaces, so “blue sky” and “bluesky” agree.

=LET(n, LOWER(TRIM(A2)),
  n1, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(n, "&", " and "), ".", ""), ",", ""), "'", ""),
  n2, " " & n1 & " ",
  n3, REDUCE(n2, {" ltd "," limited "," plc "," llp "," inc "," uk "," the "}, LAMBDA(a, w, SUBSTITUTE(a, w, " "))),
  TRIM(n3))

That formula needs Microsoft 365 for LET, REDUCE and LAMBDA; in older versions, chain SUBSTITUTE calls or use a helper column per step. Padding with spaces stops “co” from being removed from inside “cocoa”. After this step, the ACME and Smith rows match exactly on the key. Remove anything in brackets too if, like “(Leeds)”, it's a branch label rather than part of the name.

Step 2: score what's left

For names still unmatched, compare each one against the candidates and keep the best score. Common measures:

• Edit distance (Levenshtein): how many single-character edits turn one string into the other. “nortwind” to “northwind” is 1.
• Jaro-Winkler similarity: 0 to 1, favours strings that share a prefix. Good for short names with typos.
• Token overlap (Jaccard): share of words in common, robust to word order (“Traders Northwind”).

Excel has no built-in similarity function, so this step is easiest in SQL. DuckDB provides levenshtein, jaro_winkler_similarity and jaccard:

WITH pairs AS (
  SELECT l.name AS ledger_name, c.name AS contract_name,
         jaro_winkler_similarity(l.match_key, c.match_key) AS score,
         row_number() OVER (PARTITION BY l.name
           ORDER BY jaro_winkler_similarity(l.match_key, c.match_key) DESC) AS rk
  FROM ledger l CROSS JOIN contracts c
  WHERE l.match_key <> c.match_key
)
SELECT ledger_name, contract_name, round(score, 3) AS score
FROM pairs WHERE rk = 1
ORDER BY score DESC;

A cross join compares every pair, which is fine for a few thousand names each. For bigger lists, “block” first: only compare names that share a first letter or postcode, which cuts the pairs dramatically.

Step 3: thresholds and a review queue

Pick two cut-offs from your own data rather than a magic number. Sort the scored pairs and read down the list: you'll see where true matches stop and nonsense starts. A common pattern:

Of four leftover names, one was accepted automatically, two went to review (one confirmed, one rejected) and one was left unmatched. The Kingfisher row is why the review band exists: Jaro-Winkler rewards a shared opening (“king…”), so two different companies can still score above 0.85. These scores are what DuckDB's jaro_winkler_similarity returns for the normalised names shown.

• Store the confirmed pairs in a mapping table (ledger name → contract name). Next month, apply the mapping first and only score new names.
• Never let low-confidence matches flow straight into totals. Record the band next to each row so the report shows how much is auto-matched versus reviewed.
• Prefer a real ID once you have one. A supplier number or company registration number beats any name match.

Google Sheets and Excel without SQL

For small lists, you can get a long way with the normalised key plus a wildcard lookup: =XLOOKUP("*"&B2&"*", Contracts!C:C, Contracts!A:A, "", 2) finds a contract key that contains the ledger key (match mode 2 enables wildcards in Excel; in Google Sheets XLOOKUP also supports match mode 2). Containment is cruder than a score, so still review the hits.

Analistable can run the normalisation and similarity SQL above in your browser when you ask something like “match suppliers between these two sheets by name, show me the uncertain ones”, and returns the scored list for you to review. It doesn't change your source files. For other join techniques, see the join spreadsheets guide and joining on email addresses safely.

Worked example: 1,200 suppliers

A purchase ledger with 1,200 supplier names was matched against a contracts register of 950. The table below shows how many names each step resolved, which is a fair picture of what to expect: cleaning does most of the work and fuzzy scoring handles the tail.

The 312 unmatched names weren't failures: many were one-off suppliers with no contract, which is itself the finding the procurement team wanted. Reviewing 23 confirmed pairs plus the rejected ones took far less time than checking 1,200 rows by eye.

Frequently asked questions

Why match on a normalised key before fuzzy scoring?

Cleaning is deterministic and explainable, so most matches can be accepted without review. Fuzzy scores then only have to handle the small set of names that still differ, which keeps the review queue short.

Can I do fuzzy matching in Google Sheets?

There is no built-in similarity function. You can normalise names with LOWER, REGEXREPLACE and TRIM, use wildcard lookups for containment, or write a custom Apps Script function for edit distance.

Should I match on names at all?

Only when there's no shared ID. Use names to build a mapping to a real ID once, then join on the ID from then on.

Can Excel do fuzzy matching without Power Query?

Not with a built-in function. You can normalise names with formulas and use wildcard lookups, or use Microsoft's separate Fuzzy Lookup add-in for Windows Excel, or run similarity functions in SQL.

What similarity score should I use as a threshold?

There's no universal value. Score your own data, sort it, and set the auto-accept and reject cut-offs where true matches stop, with a review band in between.

Which is better, Levenshtein or Jaro-Winkler?

Jaro-Winkler suits short names with typos and rewards a shared start; Levenshtein counts edits and suits codes. Token measures such as Jaccard handle reordered words.

How do I stop re-reviewing the same names every month?

Save confirmed pairs in a mapping table and apply it before fuzzy matching, so only new or changed names need scoring.

Related guides

• How to use fuzzy merge in Power Query
• How to join two lists on email addresses safely
• How to join spreadsheets on a common column
• How to combine student grades and attendance records
• How to match support tickets to customer accounts