Fuzzy matching in Excel without Power Query: what works (2026)
Without Power Query, Excel has no built-in function that scores how similar two texts are. What works is: normalising values with formulas so that near-duplicates become exact duplicates (case, spaces, accents, word order, phone formats), Microsoft's free Fuzzy Lookup add-in on Windows for lookups between two tables, a custom LAMBDA or VBA function implementing an edit distance, or a dedicated tool. For contact lists, normalisation plus one extra field that must agree (ZIP code, city or company) removes most duplicates before any fuzzy scoring is needed.
The options compared
| Approach | Handles typos | Handles word order | Handles initials | Needs | Main drawback |
|---|---|---|---|---|---|
| Normalisation formulas + COUNTIF | No | Yes (365/2024) | No | Any Excel; TEXTSPLIT needs 365 or 2024 | Only exact matches after cleaning |
| Fuzzy Lookup add-in | Yes | Partly | No | Windows, Excel 2007–2016 listed | Lookup between two tables, not grouping |
| LAMBDA edit distance | Yes | No, unless you sort words first | No | Excel for Microsoft 365 | Slow on large lists, complex to maintain |
| VBA Jaro-Winkler / Levenshtein | Yes | If coded | If coded | Desktop Excel with macros allowed | Macros blocked in many companies |
| Power Query fuzzy grouping | Yes (Jaccard, 0.80 default) | Partly | No | Excel for Microsoft 365 | The thing you wanted to avoid |
| Browser tool such as Merge duplicate contacts | Yes (Jaro-Winkler) | Yes | Yes | A browser | Separate step outside the workbook |
Sources: Fuzzy Lookup add-in download page (system requirements), Create a fuzzy match (Jaccard, default threshold 0.80), TEXTSPLIT (Excel for Microsoft 365 and Excel 2024).
Step 1: normalise before you fuzz
Most "fuzzy" duplicates in contact lists are formatting differences. Turn them into exact matches first:
| Field | Formula idea | Before → after |
|---|---|---|
=LOWER(TRIM(B2)) | Sarah.Connor@Yahoo.com → sarah.connor@yahoo.com | |
| Phone | nested SUBSTITUTE to remove space . - ( ), then replace a leading 0 with the country code | 06.12.34.56.78 → 33612345678 |
| Name | LOWER, replace - and . by spaces, split, sort words, join | DUPONT Jean-Pierre → dupont jean pierre |
| Company | remove Ltd, Inc, LLC, GmbH, SAS, SARL with SUBSTITUTE | Baker Ltd → baker |
| ZIP | =TEXT(A2,"00000") for 5-digit codes that lost a leading zero | 6000 → 06000 |
Two traps come from Excel itself: it "automatically removes leading zeros" and keeps 15 significant digits (Microsoft support). A phone column opened from CSV may already read 612345678; your phone formula must accept that form too. Accents need one SUBSTITUTE per character in formulas, which is where most formula approaches stop.
Step 2: score what's left
After normalisation the remaining differences are spelling and initials. Two measures are common:
- Levenshtein distance counts the insertions, deletions and substitutions needed to turn one string into the other.
lefebvre→lefevreis 1. - Jaro-Winkler similarity ranges from 0 to 1 and gives extra weight to a shared beginning, which suits names. Reference values:
MARTHA/MARHTA0.961,DWAYNE/DUANE0.840.
Score word by word rather than the whole string, so Jean-Pierre Dupont vs DUPONT Jean Pierre scores 1, and treat a single letter as an initial that matches any word starting with it. Then require a second field to agree. Without that condition, every "Jean Martin" in a national list ends up in one group.
Step 3: group, don't just flag
Pairs are not enough. If row 2 matches row 3 by email and row 3 matches row 9 by phone, rows 2, 3 and 9 are one contact. This is a union-find (connected components) problem; in a spreadsheet it means repeatedly propagating the smallest group ID through matching rows until nothing changes. It is the step where formula-based approaches usually give up, and where a script or a tool is easier. The tool linked above does normalisation, Jaro-Winkler scoring, grouping and the merge in one pass, and shows the reason for every group before you download.
FAQ
Does Excel have a SOUNDEX or similarity function? No worksheet function returns a similarity score between two texts. You need the add-in, Power Query, a LAMBDA, VBA or an external tool.
Is the Fuzzy Lookup add-in still available? Microsoft's download page still lists it (Setup.exe), with Windows and Excel 2007, 2010, 2013 or 2016 as prerequisites. Newer versions are not listed there, so test it on your setup.
What threshold should I use? Power Query starts at 0.80 with Jaccard. With Jaro-Winkler on names, around 0.90 combined with a same-ZIP or same-company rule avoids most false merges; lower it only while reviewing the groups.
Why do masculine and feminine names cause false matches? Jean / Jeanne or Louis / Louise share their first letters, which Jaro-Winkler rewards. Treat a name that is another name plus e, ne or le as different.