Fuzzy matching: how it finds duplicate contacts

What it is

Exact matching asks whether two values are identical. Similarity matching asks how close they are, and treats them as the same once they are close enough. The second question is the one a contact list needs, because the same person turns up spelled, punctuated and formatted in different ways.

It is used to find duplicate rows in one list, to match rows across two lists when there is no shared ID, and to standardize free text such as survey answers against a reference list.

How it works

Every tool that does it follows the same three steps.

1. Clean the values first

Lowercase them, trim spaces, and put emails, phone numbers and company names into one form. Many duplicates are identical once that is done, and nothing needs scoring.

2. Score each pair

Give every pair of values a score for how close they are, usually from 0 to 100, where 100 means identical.

3. Keep the pairs above a threshold

Pairs that score at or above the threshold you set count as the same. The rest stay separate.

The most common score is edit distance: the number of single characters you would have to insert, delete or change to turn one value into the other. Jon into John is one insertion. Divide by the length of the longer value and you get a percentage. Other tools score differently: Power Query compares the overlap between the two values, and Salesforce uses several methods depending on the field.

A worked example: duplicate contacts in a list

Each pair below is one person in a contact list, and exact matching catches none of them. Three are caught by cleaning the values first, two by their similarity score, and one only by a person reading it.

The scores are similarity out of 100. The last row, at 67, is the limit of any letter-by-letter score: Robert and Bob share almost no characters. Nicknames need a list of name variants, or a person reading the pair.

Choosing a threshold

The threshold decides how close is close enough. Set it too high and real duplicates slip through. Set it too low and different people get merged. Scored by edit distance, these name pairs show where the line falls:

Match on more than one column where you can. Name plus company, or name plus email domain, keeps Dan Lee and Dana Lee apart when they work at different companies. And review the pairs before you merge or delete anything.

In Excel and Power Query

Excel's formulas only compare exact values, so similarity matching in Excel means Power Query, in Excel for Windows. When you merge two queries, check Use fuzzy matching to perform the merge at the bottom of the Merge dialog. It works on text columns only, in Excel for Microsoft 365, Excel 2024 and Excel 2021.

Microsoft's page lists Excel for Mac too, but the Merge dialog in Excel for Mac (version 16.113, October 2026) has no such box and no options for it, so on a Mac, Power Query merges on exact matches only, and those are case-sensitive.

Expand Fuzzy matching options to see Similarity threshold, Ignore case, Match by combining text parts, Maximum number of matches and Transformation table. Leave the threshold box empty for the default, 0.80, where 1.00 is an exact match. Power Query scores pairs by their overlap rather than by edit distance, and the merge matches rows across two tables rather than finding duplicates within one list.

Start with the default. When we merged a list of trade show leads with a CRM accounts export, 0.80 matched every company typed differently and made one wrong match. At 0.6, more than a dozen new companies matched accounts they only shared words with, such as Dental Group or Home Care.

Microsoft's older Fuzzy Lookup add-in for Excel is no longer on its download center, so Power Query is the route in current Excel.

On Windows. With the box checked, 41 of the 120 leads match an account, up from 22 matched exactly.

If a query's columns come in as Column1, Column2 and so on, with the real headers in the first row, Excel couldn't tell the header row from the data. It happens when every column is text, as in many CRM exports. Open the query, choose Home > Use First Row as Headers, and Close & Load before you merge.

In Salesforce

Salesforce finds duplicates with matching rules, set up in Setup under Matching Rules. Each field in a rule is matched exactly or by a similarity method, and the standard rules for accounts, contacts and leads already use several, such as edit distance for street names and name-variant matching for first names.

A matching rule only finds candidates. A duplicate rule, under Duplicate Rules in Setup, decides what happens: warn the user or block the save. A duplicate rule can use up to three matching rules, and each object can have up to five active matching rules.

In Python

For developers, RapidFuzz and thefuzz are the common libraries for scoring how alike two strings are, and Python's standard library has difflib for the same job. recordlinkage handles the larger task of matching whole records across files. Each takes code to set up, which is worth it for a pipeline but slow for a one-off list.

Similarity matching in Operelio

Operelio's Deduplicate tool has two modes for this, and you pick one for each run, without code. Smart matching cleans values before it compares them: Gmail addresses match with dots and plus tags ignored, phone numbers match whatever the punctuation, and company names match with suffixes like Ltd, Inc and GmbH stripped.

Similarity matching scores each pair from 0 to 100 by the number of character edits between them, and you set the threshold with a slider. It starts at 80. Pick more than one column and the score is averaged across them. Every mode is on every plan, including Free, and you can download the removed rows to check what was dropped.

Originally published in the Operelio how-tos. What threshold do you use, and what has it wrongly merged?