Fuzzy matching in Excel with Jaccard similarity

The Jaccard similarity formula

J(A, B) = |A ∩ B| ÷ |A ∪ B| = shared ÷ (|A| + |B| − shared)

The result runs from 0 (nothing in common) to 1 (identical sets). For text, you first decide what the elements of the set are: whole words (tokens) or short pieces of characters (n-grams). Bigrams, pieces of two characters, are the usual choice for names, because a typo only spoils the one or two bigrams around it while the rest still match. Take "acme tools" and "apex tools". Each has 9 bigrams (a text of length L has L − 1), and 5 are shared: " t", "to", "oo", "ol" and "ls". Jaccard = 5 ÷ (9 + 9 − 5) = 5 ÷ 13 = 0.385. The shared word "tools" is not enough to call them the same company.

Bigram Jaccard in Excel, step by step

With the two normalised names in A2 and B2:

• MID(A2,SEQUENCE(LEN(A2)-1),2) returns all bigrams of A as an array: SEQUENCE gives the start positions 1, 2, 3 … and MID takes two characters from each.
• MATCH(bigrams of A, bigrams of B, 0) looks up each bigram of A in B, ISNUMBER turns found into TRUE, and SUM(--…) counts them: the shared bigrams in column C.
• Jaccard in column D: =C2/(LEN(A2)-1+LEN(B2)-1-C2).

With LET it fits in one readable formula: =LET(ga,MID(A2,SEQUENCE(LEN(A2)-1),2), gb,MID(B2,SEQUENCE(LEN(B2)-1),2), s,SUM(--ISNUMBER(MATCH(ga,gb,0))), s/(ROWS(ga)+ROWS(gb)-s)). Strictly, Jaccard is defined on sets of distinct elements. This count-based version counts a repeated bigram of A each time, which is close enough for company names and is what many matching tools do. Whichever definition you use, use it consistently and set thresholds on it.

Worked example: bigram Jaccard and Dice for ten pairs of company names

Columns A and B hold ten pairs of normalised company names (lower case, punctuation removed). Column C counts the bigrams of name A that also occur in name B, columns D and E turn the count into the Jaccard and Dice similarity, and column F applies the two thresholds in F13 and F14. Rows 19 to 21 compare the word-level Jaccard for three pairs.

Worked example: bigram Jaccard and Dice for ten pairs of company names

	A	B	C	D	E	F
1	Name A	Name B	Shared bigrams	Jaccard	Dice	Decision
2	brightline office supply	brightline ofice supply	22	0.957	0.978	Match
3	harbor print co	harbour print co	13	0.813	0.897	Match
4	maple law group	maple law grp	11	0.733	0.846	Review
5	coral realty	coral reality	10	0.769	0.870	Match
6	north ridge cafe	northridge cafe	13	0.813	0.897	Match
7	delta logistics	delta logistic	13	0.929	0.963	Match
8	jones and smith	smith and jones	12	0.750	0.857	Match
9	willow pharma	willow farms	8	0.533	0.696	Review
10	summit dental	summit rental	10	0.714	0.833	Review
11	acme tools	apex tools	5	0.385	0.556	Non-match
12
13	Match threshold (Jaccard at least)					0.75
14	Review threshold (Jaccard at least)					0.50
15	Pairs scored Match					6
16	Pairs sent to Review					3
17	Pairs scored Non-match					1
18	Bigrams in "brightline office supply" (length − 1)					23
19	Word Jaccard, row 8 (same words, new order)					1.000
20	Word Jaccard, row 9 (one shared word of three)					0.333
21	Word Jaccard, row 6 (north ridge vs northridge)					0.250

Fictional company names. MID with SEQUENCE and TEXTSPLIT need Microsoft 365 or Excel 2021 and later (TEXTSPLIT: Microsoft 365 and Excel 2024). The thresholds are illustrative: set your own from a labelled sample.

Normalise before you compare

Similarity scores only measure what is left after cleaning. "Harbor Print Co., Inc." and "HARBOR PRINT CO" differ in case, punctuation and a legal suffix, none of which tells you anything about the customer. Clean both columns first: LOWER and TRIM for case and spaces, SUBSTITUTE for punctuation, and a short list of legal suffixes (inc, llc, ltd, gmbh) to strip. The example uses names that were already normalised that way. Without that step, exact duplicates can score below 0.8 and the thresholds stop meaning anything.

Jaccard vs Dice vs word-level Jaccard

The Dice coefficient = 2 × shared ÷ (bigrams A + bigrams B) uses the same counts and always gives the same ranking, but higher numbers: 0.556 instead of 0.385 for "acme tools". Do not mix the two when you set thresholds.

Word-level Jaccard compares whole words with TEXTSPLIT(A2," ") instead of bigrams. It handles reordering perfectly: "jones and smith" vs "smith and jones" scores 1.000 on words but only 0.750 on bigrams. It fails on a missing space: "north ridge cafe" vs "northridge cafe" shares only "cafe" and scores 0.250 on words, against 0.813 on bigrams. And it overrates different businesses with shared words: "willow pharma" vs "willow farms" scores 0.333 on words and 0.533 on bigrams. Each score catches different errors, which is why matching tools combine several. For vector-style comparisons of longer texts, see cosine similarity in Excel.

Thresholds: match, review, non-match

One threshold forces every pair into yes or no. Two thresholds create a middle band for a human to check: in the example, a Jaccard of at least 0.75 is a Match, 0.5 to 0.75 goes to Review, and anything lower is a Non-match. That gives 6 matches, 3 reviews and 1 non-match. The review band catches the hard cases: "maple law grp" (0.733) is the same firm with an abbreviation, but "summit dental" vs "summit rental" (0.714) and "willow pharma" vs "willow farms" (0.533) are different businesses with similar names. A lower match threshold of 0.7 would have merged the dentist with the rental company.

Set thresholds from data, not by feel: label a sample of pairs by hand, then measure precision and recall at several cut-offs and pick the trade-off that fits the cost of a wrong merge.

Scaling up: blocking keys

Comparing every record with every other grows fast: 10,000 customers make almost 50 million pairs, far too many for array formulas. Real record linkage first builds a blocking key, for example the first letter of the name plus the postcode, and only compares records inside the same block. That cuts the pairs by orders of magnitude, at the risk of missing duplicates whose key differs (a typo in the first letter). Score the candidate pairs with bigram Jaccard, add other evidence such as matching phone numbers or addresses, and merge the confirmed duplicates into one golden record.

Common mistakes

• Scoring raw names. Case, punctuation and "Inc." lower the score of real duplicates. Normalise both columns before comparing.
• Mixing Jaccard and Dice thresholds. Dice is always higher for the same pair. A 0.75 threshold means different things in each.
• One threshold for everything. Similar names of different businesses land just above any single cut-off. Add a review band.
• Bigrams on very short strings. "ab" has one bigram, so one typo drops the score to 0. Use exact or word matching for codes and short names.
• Comparing every pair. All-against-all comparison explodes with list size. Block on a key first.
• Guessing the cut-off. Measure precision and recall on hand-labelled pairs before trusting the matcher.