To fuzzy match in Excel, use Power Query's fuzzy merge (tick "Use fuzzy matching to perform the merge" in the Merge dialog, then set a similarity threshold between 0.00 and 1.00, default 0.80) or install Microsoft Research's Fuzzy Lookup Add-In. Neither works well on raw data: normalise the names first by stripping punctuation, expanding ampersands and removing legal suffixes such as Ltd and Limited, then match. Always review candidate pairs against a company registration number, VAT number or postcode before you merge anything, and record every decision in a mapping table.
Fuzzy matching finds records that are nearly the same rather than exactly the same. It is the standard way to find duplicate customers and suppliers in a master data extract, and the standard way to create a mess if you let it merge things automatically.
Home / Guides / Excel Fuzzy Match
Last updated: 3 August 2026 · 12 min read
Three weeks before go-live, someone finally counts the customer master. The extract has 4,812 rows. The sales director says the business deals with roughly three thousand customers. Nobody is lying. The gap is duplicates, and they are invisible to every tool the project has used, because a duplicate customer record almost never looks like a duplicate to a computer.
Here is the same supplier, six times, as it actually appears in a legacy purchase ledger:
| Record | Name as stored | Postcode |
|---|---|---|
| S00412 | Brookfield Engineering Ltd | CH41 1AY |
| S01088 | BROOKFIELD ENGINEERING LIMITED | CH411AY |
| S01931 | Brookfield Eng. Ltd. | CH41 1AY |
| S02245 | The Brookfield Engineering Co | |
| S03017 | Brookfield Engineering & Fabrication | CH41 1AY |
| S03388 | Brookfield Engineering Ltd (DO NOT USE) | CH41 1AY |
An exact match, a VLOOKUP, a pivot table and a Remove Duplicates click all agree these are six different suppliers. A human takes two seconds. This guide covers closing that gap: the Fuzzy Lookup Add-In, Power Query's fuzzy merge and fuzzy grouping, choosing a similarity threshold, why addresses behave worse than names, how to review rather than auto-merge, and when to stop doing this in Excel.
Duplicates found before go-live are a spreadsheet edit. Duplicates found after go-live are a project.
Company names are free text entered by dozens of people over fifteen years, usually while doing something else. The failure modes are consistent enough to name, because each has a specific fix.
Do not point a fuzzy algorithm at raw names. Build a normalised match key, exact-match on that key, and fuzzy match only what is left. This two-pass approach is faster, more accurate and easier to defend, because the first pass is deterministic. When the finance director asks why two accounts were merged, "the normalised names were identical" beats "the score was 0.87".
In Excel, a serviceable key needs one formula:
=TRIM(LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A2,CHAR(160)," "),"&"," and "),".",""),",",""),"'","")))That handles case, non-breaking spaces, ampersands and punctuation, but not legal suffixes or word order. For those, use Power Query: create a blank query, open the Advanced Editor, paste this function and call it as a custom column:
let
NormaliseName = (input as nullable text) as text =>
let
Raw = Text.Lower(Text.From(input ?? "")),
NoNbsp = Text.Replace(Raw, Character.FromNumber(160), " "),
Amp = Text.Replace(NoNbsp, "&", " and "),
Punct = {".", ",", "'", "(", ")", "/", "\", "-", "_", ";", ":"},
Stripped = List.Accumulate(Punct, Amp, (s, c) => Text.Replace(s, c, " ")),
Words = List.Select(Text.Split(Stripped, " "), each _ <> ""),
Noise = {"ltd", "limited", "plc", "llp", "llc", "co", "company",
"the", "uk", "holdings", "group", "international"},
Kept = List.Select(Words, each not List.Contains(Noise, _)),
Ordered = List.Sort(Kept)
in
Text.Combine(Ordered, " ")
in
NormaliseNameRun that over the six Brookfield records and five collapse to brookfield engineering. The sixth becomes and brookfield engineering fabrication, which the fuzzy pass picks up. The List.Sort step is worth stealing: sorting the words alphabetically means "Hill Andrew" and "Andrew Hill" produce the same key, turning a fuzzy problem into an exact one.
Keep the noise list short and boring. Add a word that is actually distinguishing and you will merge two real companies: "Group" is usually safe, "Northern" is not. Never strip numbers, because "Site 2" and "Site 4" are different delivery points.
The Fuzzy Lookup Add-In for Excel was built by Microsoft Research. It takes two Excel tables, compares a column in each, and returns matched pairs with a similarity score. It also deduplicates a single table by matching it against itself.
One thing to know first, from the official download page: its stated prerequisites are Excel 2007, 2010, 2013 or 2016, and its supported operating systems stop at Windows 10. Microsoft 365 and Windows 11 are not listed, and that listing has not moved in years. It still runs for many people on newer builds, but it is effectively an unmaintained research tool, and plenty of corporate IT teams now refuse to deploy it on that basis.
If you do install it:
Its strength is a ranked list of candidates with scores, exactly the shape you want for review. Its weakness is that it is a one-shot operation against a worksheet rather than a refreshable pipeline step, so on a migration where you reload the customer master a dozen times, you will run it a dozen times by hand.
Power Query is built into current Excel and Power BI, and its fuzzy matching is the supported, repeatable option that keeps working when the source is refreshed. Load both tables, select Merge Queries, choose the join columns, then tick Use fuzzy matching to perform the merge at the bottom of the Merge dialog. Expand Fuzzy matching options for the controls that matter. Per Microsoft's documentation:
| Option | What it does | Practical setting |
|---|---|---|
| Similarity threshold | Value between 0.00 and 1.00. Matches records at or above the score. 1.00 behaves as an exact match. | Default is 0.80. Start higher on normalised names. |
| Ignore case | Matches regardless of case. | On. |
| Match by combining text parts | Combines text parts, so "Micro soft" matches "Microsoft". | On for company names. |
| Show similarity scores | Returns the score alongside each match. | Always on. You cannot tune a threshold you cannot see. |
| Number of matches | Maximum matching rows returned per input row. | 3 while investigating, 1 once you trust it. |
| Transformation table | Two-column From/To table of custom value mappings applied before matching. | Your synonym and abbreviation list. |
The transformation table is the most underused of these. It is where you encode what no algorithm can infer: that "Eng" means "Engineering", that "JLR" is "Jaguar Land Rover". One behaviour will otherwise cost you an afternoon, and it is not in the documentation: matches made through the transformation table come back with a reduced score rather than a perfect one, so a threshold set very close to 1.00 can exclude them entirely. Test it on your own build before trusting a high threshold. To get the mapping without any penalty, replace the values in the column first, then match.
If your build does not expose every option in the dialog, the underlying function takes them as a record:
Table.FuzzyNestedJoin(
LegacySuppliers, {"MatchKey"},
TargetSuppliers, {"MatchKey"},
"Candidates",
JoinKind.LeftOuter,
[
IgnoreCase = true,
IgnoreSpace = true,
SimilarityColumnName = "Score",
Threshold = 0.85,
NumberOfMatches = 3,
TransformationTable = Synonyms
]
)A merge compares two tables. Deduplication compares one table against itself, so you want fuzzy grouping. Select the key column, choose Group By, and tick the fuzzy grouping option. Power Query assigns each row to a cluster and you aggregate to get a count per cluster. Anything above one is a duplicate candidate.
Table.FuzzyGroup(
Suppliers,
"MatchKey",
{
{"ClusterSize", each Table.RowCount(_), Int64.Type},
{"Members", each Text.Combine(List.Distinct([SupplierCode]), " | "), type text}
},
[IgnoreCase = true, IgnoreSpace = true, Threshold = 0.85]
)Sort descending by ClusterSize and you have your review queue, worst first. The related Cluster values feature is documented as Power Query Online only, so in desktop Excel fuzzy Group By is the equivalent.
There is no correct threshold, only a correct method for finding yours. Start high, around 0.90 on normalised names, with similarity scores on and number of matches set to 3. Sort ascending by score and read from the bottom of the accepted set upwards, looking for where pairs stop being obviously the same company and start being arguable. Step down in increments of 0.05, looking only at the pairs each step newly admitted. That delta is small enough to eyeball and tells you what each 0.05 is buying.
Two behaviours matter. Power Query uses Jaccard similarity, a set-based measure over tokens, so it compares which words two strings share rather than their order. Word order therefore matters less than you would expect and shared common words far more. And the threshold is a lower limit: Power Query assigns a value to the cluster it is closest to, above that limit.
The asymmetry that should drive your choice: a missed duplicate leaves you with two accounts, the situation you are already in, while a false merge destroys a distinction that existed in the source and may not be reconstructable. Over-report, and let review do the work.
The instinct is to concatenate name, address lines, town and postcode into one string and match that, on the theory that more information gives a better match. It does the opposite. Jaccard similarity measures shared tokens as a proportion of total tokens, and a full address is mostly common tokens: "Unit", "Road", "Industrial", "Estate", the town, the county. Two unrelated firms on the same industrial estate can share most of their address tokens and score alarmingly high, while the distinguishing part, the name, is diluted to a fraction of the string. You have built a matcher that is confident and wrong, which is worse than one that is uncertain.
Addresses are also structurally inconsistent in a way names are not. The same premises get split across Address Line 1, 2 and 3 differently by different clerks: "Unit 4" lands in line 1 on one record and line 2 on another. Matching line 1 against line 1 then fails on records describing the same place.
Use the postcode instead. It is the highest-signal element in a UK address and normalises cleanly: strip spaces, upper case it, and it becomes a near-key. Then use it for blocking. Rather than comparing every record against every other, group by outward postcode (the CH41 part) and match names within each group. Comparing everything against everything is quadratic, so five thousand records is over twelve million comparisons and twenty thousand is sixteen times that. The cost is that a pair split between a head office and a works has different postcodes and stays hidden, so run a second, unblocked pass on the residue at a high threshold.
This is where a technically successful fuzzy match becomes a business incident. The output is not a list of duplicates but a list of candidate pairs with scores, and a score is evidence, not a decision.
| Bucket | Rule | Action |
|---|---|---|
| Auto-accept | High score and a corroborating identifier matches (company registration number, VAT number, or normalised postcode plus bank account) | Merge, but still log it |
| Review | Score above threshold, no corroborating identifier, or identifiers absent | Human decision, recorded |
| Reject | Below threshold, or identifiers actively conflict | Leave alone, keep the record of having looked |
The corroborating identifier is the highest-leverage item here and the one most deduplication exercises skip. A Companies House registration number or VAT number on both records is not a similarity score, it is an answer. Where those fields are empty, as they usually are, populating them for the top few hundred suppliers beats tuning a threshold.
The reviewer should be whoever knows the accounts: credit control, purchase ledger or sales admin, not the project team. They will also catch the cases that look like duplicates and are not:
Write the survivorship rules down before review starts: typically the record with open transactions wins, then the most complete, then the oldest. Then decide field by field, because the surviving account may need the losing record's better postcode.
Never edit the source extract. Merge decisions belong in a separate mapping table, the artefact that lets you answer questions in month three and re-run the migration when a trial load fails.
| Column | Why it is there |
|---|---|
| Source system | Consolidations pull from several ledgers and the same code can exist in two of them |
| Source record ID | The legacy key, exactly as it was |
| Surviving record ID | What it maps to in the target |
| Decision | Merge, keep separate, retire |
| Match method | Exact key, fuzzy, manual, identifier match |
| Similarity score | Evidence for the decision, and the input to tuning next time |
| Corroborating identifier | Which one confirmed it, if any |
| Reviewer and date | Someone owns each decision |
This table does three jobs. It is the transformation logic for the load, so the mapping is applied by the pipeline rather than by hand. It is the repointing instruction for open transactions. And it is the answer when finance asks why the account they used for eleven years no longer exists.
Keep retired names as searchable aliases if the target supports them, because people search for what they remember rather than what survived. This is the same artefact as a source-to-target map, and the source-to-target mapping template covers the surrounding columns. The master data ownership matrix stops deduplication stalling for want of a decision-maker.
Excel is the right tool for the first pass on a few thousand records, and for showing a sceptical finance director the six Brookfields. Move to a staging database when any of these are true:
In a database the toolkit is broader. T-SQL gives you SOUNDEX and DIFFERENCE for phonetic comparison, which handles the "Smyth versus Smith" problem that character similarity struggles with, and SQL Server Integration Services ships Fuzzy Lookup and Fuzzy Grouping transformations built for this job (check current edition requirements against Microsoft's licensing documentation, because they have historically not been in every edition). Adding a Levenshtein or Jaro-Winkler function gives a second opinion alongside Jaccard, and disagreement between two algorithms flags which pairs need human eyes.
Nicknames share almost no characters with the formal name, so "Bob" and "Robert" will not match at any sensible threshold. This is exactly what a transformation table is for. Initials are the other trap: "A. R. Hill" against "Andrew Hill" scores low and is a near-certain match, so keep first initial plus surname as a secondary key.
Most legacy ledgers have a "sundry supplier" or "cash sale" account with thousands of transactions behind it, plus a long tail of one-time vendors created for a single purchase. These are not duplicates of each other and should be retired rather than merged.
Where records are individuals rather than businesses, the surviving record inherits history from the retired one and can outlive the basis on which the original data was held. Settle retention and lawful basis before merging.
Before go-live, a merge decision is a row in a mapping table. You change it, you reload, you move on. After go-live, both records have started accumulating: orders raised against each, invoices posted, payments allocated, delivery addresses and payment terms set independently. Merging then means either a data fix that must preserve transactional integrity across the ledger, or living with the split permanently. Most ERP systems, Infor LN included, will not let you delete a customer or supplier with transaction history. The most you can do is block it, so the duplicate stays for the life of the installation and every user has to know which one is the good one.
The consequences are not abstract:
This is why deduplication belongs in migration readiness rather than post go-live cleanup. The data quality audit checklist covers the domains beyond customers and suppliers, and the ERP data migration checklist puts them in cutover sequence.
If your customer or supplier master is in the state described above and you would rather not spend a fortnight tuning thresholds, this is the work I do. As a data quality consultant I profile the master data, quantify the duplicates and the rest of the defects, and build the deduplication and mapping process so it is repeatable and auditable rather than a one-off spreadsheet exercise. Delivery is ISO 9001 certified.
If a go-live is already in the diary, start with a data migration readiness review: a fixed-scope assessment of what is in the legacy data, what will fail on load, and what will pass validation while being wrong, which is the more dangerous category. Duplicate customers and suppliers are typically the largest single finding, and the one with the shortest window to fix cheaply.
It is still on the Microsoft Download Centre and still installs for many people, but treat it as unmaintained. The download page lists Excel 2007, 2010, 2013 and 2016 as prerequisites and stops at Windows 10, with no mention of Microsoft 365 or Windows 11. For anything repeatable, use Power Query fuzzy merge instead.
Power Query defaults to 0.80. For names you have already normalised, start around 0.85 to 0.90 and walk down in steps of 0.05, reviewing only the pairs each step newly admits. Always enable similarity scores, because tuning blind is guesswork.
Yes. Power Query supports fuzzy merge between two tables and fuzzy Group By within one. Tick "Use fuzzy matching to perform the merge" in the Merge dialog. It only works on text columns, so set the column type to Text first. Cluster values is Power Query Online only, so in desktop Excel use fuzzy Group By.
Power Query uses Jaccard similarity, comparing sets of tokens rather than character sequences. Common tokens such as Engineering, Services or a shared town name appear in many unrelated records, so the more generic text in the match column, the higher unrelated records score. Strip the noise words and use postcode and registration number as corroboration instead.
Before, without exception. Beforehand a merge is an edit to a mapping table and a reload. Afterwards, orders, invoices and open receivables have attached to both records, and most ERP systems will not let you delete a master record with transaction history. You can generally only block it, so the duplicate remains permanently.
Deduplication without governance is a treadmill. Decide who may create a customer or supplier, require the registration or VAT number at creation, and add a duplicate check at the point of entry rather than relying on periodic clean-ups. Naming standards help only if one named person owns them, and that is worth settling while there is still a project to settle it in.