How to Fuzzy Match Two Lists in Excel (4 Methods)

You have two lists that should line up: vendors in the ERP and vendors on last quarter's invoices, customers in the CRM and customers in the billing export. A VLOOKUP finds almost nothing because one side says "Acme Corp." and the other says "ACME Corporation", or "Metro Logstics" has a typo, or someone added two spaces. Fuzzy matching solves this by scoring how similar two pieces of text are instead of asking whether they are identical.

This guide walks through four ways to fuzzy match two lists in Excel, from the free Microsoft add-in to a dedicated tool, with the settings that matter and the mistakes that cause bad matches.

Before you match: why exact lookups fail

Most "no match" results come from a short list of causes. Knowing them helps you pick the right method and set a sensible threshold.

• Case and spacing: "north america", "North America ", "NORTH AMERICA".
• Punctuation and symbols: "Smith & Sons" vs "Smith and Sons", "Acme, Inc." vs "Acme Inc".
• Legal suffixes: Ltd vs Limited, Corp vs Corporation, LLC, GmbH, Pvt.
• Abbreviations: Intl vs International, Mfg vs Manufacturing, St vs Street.
• Typos and transposed letters: "Logstics", "Recieve".
• Word order: "Supplies ABC Industrial" vs "ABC Industrial Supplies".

A good fuzzy match setup handles the first three by normalizing the text before comparing, and the last three with a similarity score.

Method 1: Microsoft's Fuzzy Lookup add-in

Fuzzy Lookup is a free add-in from Microsoft for desktop Excel on Windows. It has been around for years and still does the job for one-off matching between two tables.

1. Download "Fuzzy Lookup Add-In for Excel" from the Microsoft Download Center and install it. A Fuzzy Lookup tab appears in the ribbon.
2. Turn both lists into Excel tables (select the range, press Ctrl+T). Give them clear names like tblERP and tblInvoices.
3. Click an empty cell where the result should go, then Fuzzy Lookup on the ribbon.
4. Pick the left table and the right table, then the column to match on each side, and click the join button between them.
5. Choose the output columns, set the number of matches (1 is usual) and the similarity threshold, then click Go.

The output includes a Similarity column from 0 to 1. Sort by it and review the lowest scores first.

Good for: a quick match without Power Query knowledge. Watch out for: it only works in desktop Excel on Windows, the result is a static table (re-run it when the data changes), and large tables can be slow.

Method 2: Power Query fuzzy merge

If you have Excel for Microsoft 365 on Windows, Power Query can do fuzzy merges natively, and the result refreshes when the source lists change. This is the better option for anything you will repeat monthly.

1. Select the first list and choose Data > From Table/Range. In the Power Query editor, choose Close & Load To > Only Create Connection. Repeat for the second list.
2. Data > Get Data > Combine Queries > Merge.
3. Pick the two queries and click the matching column in each.
4. Tick "Use fuzzy matching to perform the merge" and open Fuzzy matching options.
5. Set the similarity threshold (the default is 0.80), tick "Ignore case", and tick "Match by combining text parts" if spacing varies ("NextGen" vs "Next Gen").
6. Set "Maximum number of matches" to 1 if you want one best candidate per row.
7. Click OK, expand the merged column, and load the result to a sheet.

Power Query also accepts a transformation table: a two-column table with "From" and "To" values that tells it "Intl" means "International" before scoring. That is the cleanest way to teach it your own abbreviations.

Good for: repeatable matching that refreshes. Watch out for: there is no per-row explanation of why two values matched, so checking borderline pairs is manual. Adding a similarity score column takes extra steps (in the fuzzy options you can name a similarity column).

Method 3: Formulas (normalize, then match)

Formulas cannot score similarity out of the box, but you can get a long way by cleaning both sides into a "match key" and then using an exact lookup. This often fixes more than half of the misses with no add-ins.

In a helper column on each list, build a normalized key. For a name in A2:

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",""),"&","and")))

Then strip common suffixes. In Excel for Microsoft 365 you can wrap the key in a short LET or nest further SUBSTITUTE calls to remove " inc", " ltd", " llc", " corporation" and " corp". Finally match on the keys:

=XLOOKUP(C2, tblInvoices[Key], tblInvoices[Vendor], "no match")

For "contains" matches, XLOOKUP accepts wildcards with match mode 2: =XLOOKUP("*"&C2&"*", tblInvoices[Key], tblInvoices[Vendor], "no match", 2). Use it carefully; a short key like "abc" will match the first of many names that contain it.

Good for: no installs, fully transparent. Watch out for: typos and reordered words still fail, and the suffix list grows forever.

Method 4: A dedicated matching tool

When matching is part of a regular process, such as monthly vendor reconciliation or cleaning a customer master, a dedicated tool saves time because it normalizes the text for you, gives a score you can sort on, and tells you why two values were considered the same.

QikAnalytix (the Windows desktop app we build) has a Fuzzy match tool for this. It compares two text columns on the same row and adds three columns:

• Match_Score: similarity from 0 to 100.
• Match_Status: AUTO MATCH (95 and above by default), REVIEW (85 and above) or NO MATCH.
• Match_Why: the reason, such as "Exact match after normalization", "shared tokens: metro, logistics" or "numeric tokens disagree, score capped".

It has three modes. General treats case, punctuation, "&" vs "and", spacing and common legal suffixes (Ltd, Limited, Pvt, Corp, Inc, Co, LLC) as the same. Name / Company also ignores legal-form words, knows nicknames (Bob and Robert) and business abbreviations (Mfg, Intl), and catches names that sound alike. Address knows Rd and Road, St and Street, Ave, Blvd, Apt and similar. You can also tick a box to get the Levenshtein, Jaro-Winkler, token set and phonetic sub-scores.

One thing to know: it scores pairs that are already side by side. If your two lists are separate tables, bring the values onto the same row first. In a reconciliation that usually means joining on something both sides share, such as the vendor ID or invoice number, and then fuzzy matching the names to find the rows where the ID matches but the name does not. That is exactly where wrong payments and duplicate vendors hide. If there is no shared key at all, a Power Query fuzzy merge (Method 2) is the right tool to find candidate pairs.

Everything runs on your PC, which matters when the lists contain customer or supplier details you would rather not upload to a web-based matching service.

Choosing a threshold

Whatever method you use, the threshold decides how much checking you do later. A practical approach:

1. Run the match with a moderate threshold (0.80, or 85 on a 0 to 100 scale).
2. Sort by score. Read 20 pairs just above the cutoff and 20 just below.
3. If you find wrong matches above the cutoff, raise it. If you find good matches below, lower it.
4. For anything that moves money or changes master data, keep a review band and have a person approve it.

Common mistakes

• Skipping cleanup. Trim spaces and fix case first. Matches get better and the results are easier to read.
• Trusting a score as proof. A similarity score is a hint, not an answer. Review the borderline band.
• Ignoring numbers. "Store 104" and "Store 140" look similar to most algorithms. Check numeric parts separately.
• Matching on too little text. Very short names ("ABC", "Lee") produce many false matches. Add a second field such as city or postcode to the key.
• Doing it once. If you match every month, build it in Power Query or a tool that replays the steps, not in a static sheet.

Which method should you use?

FAQ

Does Excel have a built-in fuzzy match function?

Not as a worksheet function. Excel offers fuzzy matching through Power Query's Merge Queries option and through Microsoft's free Fuzzy Lookup add-in for desktop Excel on Windows. Formulas like XLOOKUP only do exact or wildcard matches.

What similarity threshold should I use?

Start around 0.80 in Power Query or Fuzzy Lookup, or 85 on a 0 to 100 scale, and review everything near the cutoff. Raise the threshold when a wrong match is costly, such as payments or master data, and lower it when you would rather review more candidates.

Why do my fuzzy matches pair Unit 12 with Unit 21?

Most similarity measures look at shared characters, so numbers that differ by one digit still score high. Check pairs where the numbers disagree by hand, or use a tool that caps the score when numeric parts of the text do not match.

Want a score, a status and a reason for every pair, without uploading your vendor list anywhere? Try QikAnalytix free for 7 days. It runs 100% offline on Windows 10 and 11, no credit card needed.