Names that should match but do not are a daily Sheets problem: "Smith, John" against "John Smith", "Jon" against "John", "Dr. J. Smith" against "John Smith". Standard lookups need an exact match. This page starts with cleaning, which fixes most cases, then moves to similarity scores for what is left.
Names A2:A4: Smith, John / JOHN SMITH / Jon Smith. The master list is John Smith, in D2, with ID 101 in E2. Row 1 holds headers.
Most "typos" are formatting. Fix those first and a normal lookup works.
=IF(ISNUMBER(SEARCH(",",A2)),TRIM(REGEXREPLACE(A2,"^\s*([^,]+),\s*(.+)$","$2 $1")),TRIM(A2))
"Smith, John" becomes "John Smith". Names without a comma are only trimmed.
=TRIM(REGEXREPLACE(A2,"(?i)\b(mr|mrs|ms|miss|dr|jr|sr)\b\.?",""))
"Dr. John Smith Jr" becomes "John Smith". Check your data first: a surname that is also a title word will be removed too.
This builds a key by removing punctuation, lowercasing, splitting into words and sorting them:
=IF(A2="","",TEXTJOIN(" ",TRUE,SORT(TRANSPOSE(SPLIT(LOWER(TRIM(REGEXREPLACE(A2,"[^\w\s]","")))," ")))))
"Smith, John", "John Smith" and "JOHN SMITH" all become "john smith". Build the same key for your master list in column F, then look up, where G2 holds the key for the messy name:
=IFERROR(INDEX($E$2:$E$100,MATCH(G2,$F$2:$F$100,0)),"")
The first two sample names return 101. "Jon Smith" returns blank, because the spelling differs inside a word. That is what the next steps are for. Removing punctuation turns O'Brien into OBrien, on both sides, so it still matches itself.
Cleaning cannot fix a spelling difference inside a word. A common compromise is a key of first initial plus last word:
=IF(A2="","",LOWER(LEFT(TRIM(A2),1)&" "®EXEXTRACT(TRIM(A2),"\S+$")))
"Jon Smith", "John Smith" and "J. Smith" all become "j smith". The catch is false positives: John Smith and Jane Smith get the same key. Flip "Last, First" names first, otherwise "Smith, John" gives "s john". Use this key to build a shortlist, then check the pairs by eye.
When the cleaning approaches are not enough, you need a similarity score, a number from 0 to 1 for how close two strings are. Google Sheets has no built-in function for it. You can build one from formulas alone with LAMBDA, as our Levenshtein guide shows: https://www.snakbacks.com/levenshtein-distance-google-sheets or get that formula ready-made in our free starter template: https://snakback.gumroad.com/l/levenshtein-starter
Another free option is a small Apps Script function (Extensions > Apps Script) that scores a pair using Levenshtein distance, the number of single-character edits between two strings:
function NAMESCORE(a, b) {
a = String(a).toLowerCase().trim();
b = String(b).toLowerCase().trim();
if (!a && !b) return 1;
var m = a.length, n = b.length, prev = [], cur, i, j;
for (j = 0; j <= n; j++) prev[j] = j;
for (i = 1; i <= m; i++) {
cur = [i];
for (j = 1; j <= n; j++) {
cur[j] = Math.min(prev[j] + 1, cur[j - 1] + 1,
prev[j - 1] + (a[i - 1] === b[j - 1] ? 0 : 1));
}
prev = cur;
}
return 1 - prev[n] / Math.max(m, n);
}
Then use =NAMESCORE(A4, $D$2). "Jon Smith" against "John Smith" is one edit over 10 characters, so 0.9. This scores one pair at a time. To find the best match in a list you would need a grid of scores, or a longer script that loops over the list.
Two cautions before you trust a cut-off such as 0.85:
Short names cross it too easily, because the score divides one edit by the length. "Ann Lee" against "Ann Lea" scores 0.857 and "Jon Smith" against "Jan Smith" scores 0.889, yet both are different people. Make the cut-off stricter for short names, or send anything that scores below 1 to a "check by eye" column instead of accepting it automatically.
Score the cleaned key from Step 1, not the raw cell. Raw "Smith, John" against "John Smith" scores only 0.09, although the sorted key already shows they are the same name. =NAMESCORE(G4, $F$2) on the key columns gives 1.
(Thanks to a reader on our Hashnode blog for pointing out both of these.)
No method here understands that "Bob" and "Robert" are the same person. For nicknames, keep a small lookup table of nickname and full name and replace first. Names in different scripts or with accents also need preparing first.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. It gives you FUZZYSCORE(a, b) for the 0 to 1 score, FUZZYMATCH(values, lookup, [threshold]) to return the closest name from a clean list, and FUZZYLOOKUP to fetch an ID alongside it, each accepting a whole column. You paste the functions in once, or copy a template sheet, so it works where a Workspace admin blocks add-ons, and you own the code. There is a 30-day refund if it does not work for you. If the cleaning steps above fix your data, you do not need it.
Our 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.