Levenshtein distance counts the single-character edits (insert, delete or replace) needed to turn one text into another. "John Smith" and "Jon Smith" are 1 edit apart. It is the standard way to measure how close two spellings are. Google Sheets has no built-in function for it, but LAMBDA, REDUCE and SCAN make a pure formula possible, with no Apps Script. This page gives the formula, turns it into a 0 to 1 similarity score, saves it as a reusable named function, and uses it to find the closest match in a list.
Row 1 holds headers. Pairs in A2:B7:
kitten, sitting
John Smith, Jon Smith
Acme Pty Ltd, ACME Pty. Ltd
Initech, Initech
Globex, Globex Corporation
(blank), abc
In C2:
=LET(a,LOWER(A2),b,LOWER(B2),m,LEN(a),n,LEN(b),IF(m=0,n,IF(n=0,m,INDEX(REDUCE(SEQUENCE(1,n+1,0),SEQUENCE(m),LAMBDA(prev,i,SCAN(i,SEQUENCE(1,n+1,0),LAMBDA(left,j,IF(j=0,i,MIN(INDEX(prev,1,j+1)+1,left+1,INDEX(prev,1,j)+(MID(a,i,1)<>MID(b,j,1)))))))),1,n+1))))
Fill it down. Results: 3, 1, 1, 0, 12, 3. kitten to sitting is the textbook example: replace k with s, replace e with i, add g. LOWER makes it ignore capitals, so "Acme Pty Ltd" and "ACME Pty. Ltd" differ only by the full stop. Remove both LOWER calls if case should count.
How it works, briefly: the classic method fills a grid with one row per character of the first text. Each cell is the cheapest of three moves: delete (the cell above plus 1), insert (the cell to the left plus 1), or replace (the cell diagonally above-left, plus 1 if the two characters differ). SCAN builds one row from left to right, REDUCE feeds each finished row into the next, and the bottom-right cell is the answer. Only one row is kept at a time, so it stays small.
A raw distance is hard to compare across lengths: 3 edits is a lot for a 6-letter word and very little for a 40-character address. Divide by the longer length and subtract from 1. In D2:
=IF(MAX(LEN(A2),LEN(B2))=0,1,1-C2/MAX(LEN(A2),LEN(B2)))
Results: 0.571, 0.9, 0.923, 1, 0.333, 0. Format the column as a number with 3 decimals.
Notice Globex against Globex Corporation: 0.333, although they are clearly the same company. Levenshtein compares characters in order, so extra words cost one edit per letter. Strip suffixes such as Pty Ltd and Corporation before you score company names. Our guide to standardising company names shows a formula for that: https://www.snakbacks.com/standardize-company-names-google-sheets
The formula is long, so save it once and call it like a built-in function:
Data > Named functions > Add new function.
Name: LEVENSHTEIN. Argument placeholders: text1, text2.
Formula definition: paste the formula from above, with A2 replaced by text1 and B2 by text2.
Click Next, then Create.
Now =LEVENSHTEIN(A2,B2) gives the distance. Named functions belong to the spreadsheet; to use one in another file, choose Import function in the same panel.
Prefer it ready-made? Our free Levenshtein & Similarity Starter is a Google Sheets template with this named function plus a similarity tab and a best-match tab already set up. Enter $0 to get it: https://snakback.gumroad.com/l/levenshtein-starter
Master list in F2:F5: Initech, Umbrella Corp, Acme Pty Ltd, Globex Corporation. Messy names in H2:H4: Acme Pty. Ltd., Globex Corp, Inytech. In I2, using the named function:
=LET(list,$F$2:$F$5,s,MAP(list,LAMBDA(x,1-LEVENSHTEIN(H2,x)/MAX(LEN(H2),LEN(x)))),HSTACK(XLOOKUP(MAX(s),s,list),MAX(s)))
Fill down. It returns the best match and its score: Acme Pty Ltd 0.857, Globex Corporation 0.611, Initech 0.857. MAP scores every name in the list and XLOOKUP picks the highest.
Rank by similarity, not by raw distance. Globex Corp is 7 edits from Globex Corporation, and also 7 from Umbrella Corp, so a smallest-distance lookup returns Umbrella Corp, the first of the tie. Dividing by length breaks the tie the right way: 0.611 against 0.462.
Then decide on a cut-off. Accept matches above it and check the rest by eye. Be careful with short names: one edit in a 7-letter name still scores 0.857, which is how "Initech" and "Initec" can look like a match when they might be two companies.
Speed. Every pair builds a grid of LEN(a) by LEN(b) steps, and the closest-match formula scores every pair. 500 names against a 500-name list is 250,000 pairs, and Sheets will slow to a crawl well before that. The formula suits a few hundred comparisons, not thousands.
Word order. "Smith, John" and "John Smith" score low, because Levenshtein reads left to right. Sort the words first if order does not matter; our names guide shows how: https://www.snakbacks.com/match-names-with-typos-google-sheets
Accents count as different characters: "José" against "Jose" is 1 edit.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
The named function above is free and fine for small lists. For thousands of rows, the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas gives FUZZYSCORE, FUZZYMATCH and FUZZYLOOKUP as Apps Script functions that take whole columns. In our test, 1,000 names against a 1,000-name list finished in about 15 seconds. Its score also handles reordered words and extra words, so "Globex" against "Globex Corporation" scores 0.867 instead of 0.333, and "Smith, John" against "John Smith" scores 1. There is a 30-day refund if it does not work for you.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.