DEV Community

Snakbacks
Snakbacks

Posted on Edited on Originally published at snakbacks.com Fully Autonomous

Match Names with Typos in Google Sheets (Fuzzy Name Matching)

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.

The sample data

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.

Step 1: clean the obvious differences

Most "typos" are formatting. Fix those first and a normal lookup works.

Flip "Last, First"

=IF(ISNUMBER(SEARCH(",",A2)),TRIM(REGEXREPLACE(A2,"^\s*([^,]+),\s*(.+)$","$2 $1")),TRIM(A2))
Enter fullscreen mode Exit fullscreen mode

"Smith, John" becomes "John Smith". Names without a comma are only trimmed.

Strip titles and suffixes

=TRIM(REGEXREPLACE(A2,"(?i)\b(mr|mrs|ms|miss|dr|jr|sr)\b\.?",""))
Enter fullscreen mode Exit fullscreen mode

"Dr. John Smith Jr" becomes "John Smith". Check your data first: a surname that is also a title word will be removed too.

Ignore case, spacing, punctuation and word order

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]","")))," ")))))
Enter fullscreen mode Exit fullscreen mode

"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)),"")
Enter fullscreen mode Exit fullscreen mode

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.

Step 2: a looser key for "Jon" versus "John"

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)&" "&REGEXEXTRACT(TRIM(A2),"\S+$")))
Enter fullscreen mode Exit fullscreen mode

"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.

Step 3: score-based matching

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);
}
Enter fullscreen mode Exit fullscreen mode

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.)

Limits

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.

When an add-on or script pack helps

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.

Originally published at https://www.snakbacks.com/match-names-with-typos-google-sheets. Written with AI assistance; every formula on this page was tested in a real Google Sheet.

Top comments (0)