Fuzzy match two lists

How to fuzzy match two lists

1. Paste both lists. One value per line. Copy a column straight out of Excel or Google Sheets — a copied column arrives as one value per line already.
2. Set how close is close enough. The slider is the score a pair must reach to count as a match. 80% is a sensible starting point for company and person names.
3. Read the score on every row. Each value in list A shows its best counterpart and the score it earned, so a borderline pair is visible rather than silently accepted.
4. Move the threshold and watch. Raise it until the wrong pairs drop out, lower it until the right ones come back. Then download the result as CSV.

What fuzzy matching is

Fuzzy matching pairs two values that refer to the same thing but are not written the same way. Exact matching asks "is this string in the other list"; fuzzy matching asks "which entry over there is this one, spelled differently" — and answers with a score between 0 and 100% rather than a yes or a no.

It is the tool for the job when two systems disagree about the same customer: Acme Corp. and ACME Corporation, Jon Smith and John Smith, Smith & Co Ltd and Ltd Smith and Co. If your two lists hold identical values and you just need the overlap, exact list comparison is faster and has no threshold to get wrong.

Three ways two values can be close

A single similarity measure gets one of these right and the other two wrong, so each pair is scored three ways and keeps its best result:

• Mistyped. Edit distance, counting a swapped pair of letters as one mistake rather than two — the most common typing error there is. Micheal against Michael scores 93%.
• Reordered. The same comparison run on the sorted, de-duplicated words. Corp Acme against Acme Corp scores 100%; on raw edit distance it would score about 40%.
• Abbreviated. How well the shorter value sits inside the longer one, scaled down so a short string is not declared a match for every long string that contains it. Acme Corp. against ACME Corporation scores 90%.

Before any of that, both sides are trimmed, folded to a single case, normalized for full-width characters and accents (Unicode NFKC), and stripped of punctuation and repeated spaces — so Acme Corp. and Acme Corp never reach the scoring stage as different values at all.

Numbers are identifiers, not spelling

Invoice 1001 and Invoice 1002 are 92% similar as text, and they are two different invoices. Fuzzy matching that silently pairs them is worse than no fuzzy matching at all, because the error is invisible in the output. So when both values contain digits and the digits differ, the pair is held below the threshold no matter how similar the letters are. Order numbers, SKUs, account numbers and postcodes are safe by default. The checkbox turns the rule off if your data uses numbers as words rather than as identifiers.

Why this is not the Fuzzy Lookup Add-In

Microsoft's Fuzzy Lookup Add-In does this job well and has two costs: it is a separate download that only runs in Excel on Windows, and in many organizations installing an add-in is an IT request rather than a decision. The Power Query route needs no download but does need a merge step, a similarity option and a re-run each time the data changes. Both give you a threshold and neither shows you, per row, what score a pair actually earned — which is the number you need to decide where the threshold belongs.

This page shows the score on every row, recomputes as you drag the slider, and runs anywhere a browser does, including on a Mac.

What it will not do

Being explicit about the limits, because a matching tool that overstates itself costs more than it saves:

• Abbreviations it cannot know. Ltd against Limited scores 70% and needs the threshold lowered. There is no dictionary of legal forms behind this — matching is on the characters, not on meaning.
• One answer per value. Each entry in list A gets its single best counterpart, the way a lookup behaves. It does not return every candidate above the threshold.
• Two lists, not two tables. Paste the columns you want matched. To diff whole files column by column, use compare two Excel files.

Nothing leaves this tab

The lists people fuzzy match are customer names, supplier names and payroll rosters — the kind of data that is awkward to paste into someone else's server. Both lists are scored by JavaScript running in this tab. There is no request to send, so nothing to intercept, log or retain.