<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Snakbacks</title>
    <description>The latest articles on DEV Community by Snakbacks (@snakbacks_sheets).</description>
    <link>https://dev.to/snakbacks_sheets</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4253192%2Fb27b7e10-6b01-4a35-a40b-1fb5e7401364.png</url>
      <title>DEV Community: Snakbacks</title>
      <link>https://dev.to/snakbacks_sheets</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/snakbacks_sheets"/>
    <language>en</language>
    <item>
      <title>How to Fuzzy Match in Google Sheets (3 Ways)</title>
      <dc:creator>Snakbacks</dc:creator>
      <pubDate>Thu, 19 Nov 2026 07:08:44 +0000</pubDate>
      <link>https://dev.to/snakbacks_sheets/how-to-fuzzy-match-in-google-sheets-3-ways-5cc6</link>
      <guid>https://dev.to/snakbacks_sheets/how-to-fuzzy-match-in-google-sheets-3-ways-5cc6</guid>
      <description>&lt;p&gt;Google Sheets has no built-in fuzzy match. VLOOKUP, MATCH and XLOOKUP only find text that is identical, so "Inytech" will never find "Initech", and "ACME Pty. Ltd" will never find "Acme Pty Ltd". This page shows three ways to fix that, starting with the ones that need no add-on.&lt;/p&gt;

&lt;h2&gt;
  
  
  The problem in one example
&lt;/h2&gt;

&lt;p&gt;You have a clean master list of customers with IDs, and a messy list from another system. You want the ID next to every messy name.&lt;/p&gt;

&lt;p&gt;Master list (columns D and E):&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Initech, 101&lt;/li&gt;
&lt;li&gt;Umbrella Corp, 102&lt;/li&gt;
&lt;li&gt;Acme Pty Ltd, 103&lt;/li&gt;
&lt;li&gt;Globex Corporation, 104&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Messy names (column A, starting at A2):&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;ACME Pty. Ltd&lt;/li&gt;
&lt;li&gt;Inytech&lt;/li&gt;
&lt;li&gt;Corp Umbrella&lt;/li&gt;
&lt;li&gt;Globex&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;A normal VLOOKUP returns #N/A for all four. Each one fails for a different reason: punctuation, a typo, word order and a shortened name. No single trick fixes all four, which is why there are three methods below.&lt;/p&gt;

&lt;h2&gt;
  
  
  Method 1: clean both sides, then look up (no add-on)
&lt;/h2&gt;

&lt;p&gt;This handles case, extra spaces and punctuation. It does not handle typos or word order.&lt;/p&gt;

&lt;p&gt;In F2 on the master list, build a clean key and fill it down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight datastudio"&gt;&lt;code&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="nb"&gt;LOWER&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;TRIM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;REGEXREPLACE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;D2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s2"&gt;"[^\w\s]"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s2"&gt;""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then in B2, clean the messy name the same way and look it up against the key column:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFERROR(INDEX($E$2:$E$100, MATCH(LOWER(TRIM(REGEXREPLACE(A2,"[^\w\s]",""))), $F$2:$F$100, 0)), "")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"ACME Pty. Ltd" becomes "acme pty ltd", which matches the key for Acme Pty Ltd, so you get 103. The other three rows return blank, because they are not just a formatting difference.&lt;/p&gt;

&lt;p&gt;If your spreadsheet uses a comma as the decimal mark, Sheets may want semicolons between arguments instead of commas.&lt;/p&gt;

&lt;h2&gt;
  
  
  Method 2: wildcards and word overlap (no add-on)
&lt;/h2&gt;

&lt;p&gt;Use this when one name is a shortened version of the other.&lt;/p&gt;

&lt;h3&gt;
  
  
  Wildcard match
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFERROR(INDEX($E$2:$E$100, MATCH("*"&amp;amp;A2&amp;amp;"*", $D$2:$D$100, 0)), "")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The asterisks mean "anything before or after". "Globex" is found inside "Globex Corporation", so you get 104. This only works when your messy text is contained in the master name, in the same word order.&lt;/p&gt;

&lt;h3&gt;
  
  
  Word overlap, for different word order
&lt;/h3&gt;

&lt;p&gt;Put the messy name in G1, for example "Corp Umbrella". Next to each master name, in H2, count how many of its words appear in the candidate, and fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=SUMPRODUCT(--ISNUMBER(SEARCH(SPLIT(LOWER(REGEXREPLACE(TRIM($G$1),"[^\w\s]",""))," "),LOWER(D2))))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then pick the highest score:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(MAX(H2:H100)=0,"",INDEX(D2:D100,MATCH(MAX(H2:H100),H2:H100,0)))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"Corp Umbrella" scores 2 against Umbrella Corp, 1 against Globex Corporation and 0 against the rest, so it returns Umbrella Corp.&lt;/p&gt;

&lt;p&gt;Limits: it matches word fragments (the word "corp" is found inside "Corporation", which is why that row scores 1), and a single typo inside a word scores zero. Check the best match by eye, and only trust it when the winner clearly beats the runner-up.&lt;/p&gt;

&lt;h2&gt;
  
  
  Method 3: typo-tolerant matching
&lt;/h2&gt;

&lt;p&gt;To catch "Inytech" you need an edit-distance measure, which counts how many single-letter changes turn one string into another. Sheets does not have one, so you add it yourself.&lt;/p&gt;

&lt;h3&gt;
  
  
  Option A: a small Apps Script function (free)
&lt;/h3&gt;

&lt;p&gt;Open Extensions &amp;gt; Apps Script, paste this, and save:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight javascript"&gt;&lt;code&gt;&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nc"&gt;CLOSEST_MATCH&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;list&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;minScore&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;target&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;norm_&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;value&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;min&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;minScore&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="kc"&gt;undefined&lt;/span&gt; &lt;span class="o"&gt;||&lt;/span&gt; &lt;span class="n"&gt;minScore&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="s2"&gt;""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="mf"&gt;0.8&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;minScore&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;concat&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;apply&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;list&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;best&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;""&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;bestScore&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;cand&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;norm_&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;!&lt;/span&gt;&lt;span class="n"&gt;cand&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;continue&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;score&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="nf"&gt;lev_&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;target&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;cand&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="nb"&gt;Math&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;target&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;cand&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;score&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;bestScore&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="n"&gt;bestScore&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;score&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;best&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;bestScore&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="n"&gt;min&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="n"&gt;best&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;""&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nf"&gt;norm_&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nb"&gt;String&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;toLowerCase&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;replace&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="o"&gt;^&lt;/span&gt;\&lt;span class="n"&gt;w&lt;/span&gt;\&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;&lt;span class="n"&gt;g&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;replace&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;/&lt;/span&gt;\&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="o"&gt;+/&lt;/span&gt;&lt;span class="n"&gt;g&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;" "&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nf"&gt;lev_&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
      &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;cost&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;charAt&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;charAt&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
      &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;Math&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;cost&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="n"&gt;prev&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Use it in a cell, one row at a time, and fill down:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=CLOSEST_MATCH(A2, $D$2:$D$100, 0.8)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"Inytech" returns Initech. "Corp Umbrella" still returns blank, because the letters are in a different order. Custom functions are slow on thousands of rows, since each cell scans the whole list.&lt;/p&gt;

&lt;h3&gt;
  
  
  Option B: the Fuzzy Match add-on (free)
&lt;/h3&gt;

&lt;p&gt;Disclosure: Snakbacks, the publisher of this site, makes a free add-on called Fuzzy Match: Fuzzy VLOOKUP for Sheets. It adds three formulas that tolerate typos, case, punctuation and word order, and each accepts a whole column:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=FUZZYMATCH(A2:A100, Customers!A2:A, 0.8)
=FUZZYLOOKUP(A2:A100, Customers!A2:A, Customers!C2:C, 0.8)
=FUZZYSCORE(A2, B2)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;FUZZYMATCH returns the closest value from your clean list. FUZZYLOOKUP is like XLOOKUP but fuzzy: it finds the closest row and returns any column from it, such as an ID. FUZZYSCORE returns a similarity from 0 to 1. Everything runs inside your sheet.&lt;/p&gt;

&lt;p&gt;It is coming soon to the Google Workspace Marketplace.&lt;/p&gt;

&lt;p&gt;If you cannot install add-ons, or prefer to own the code: Fuzzy Match Template for Google Sheets (US$9, no add-on install): &lt;a href="https://snakback.gumroad.com/l/fuzzy-formulas" rel="noopener noreferrer"&gt;https://snakback.gumroad.com/l/fuzzy-formulas&lt;/a&gt;. It is the same three formulas as Apps Script you paste in once, or get by copying a template sheet. Snakbacks sells this pack.&lt;/p&gt;

&lt;h2&gt;
  
  
  Which method should you pick?
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Only case, spaces or punctuation differ: Method 1. Quick and exact.&lt;/li&gt;
&lt;li&gt;Names are shortened versions of each other: Method 2, wildcard.&lt;/li&gt;
&lt;li&gt;Word order differs: Method 2 word overlap, or the add-on.&lt;/li&gt;
&lt;li&gt;Typos: Method 3, either option.&lt;/li&gt;
&lt;li&gt;Thousands of rows, repeated often: the add-on, since the formulas fill a whole column in one go.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Choosing a threshold
&lt;/h2&gt;

&lt;p&gt;Fuzzy matching returns a score from 0 (nothing alike) to 1 (identical). You decide the cut-off:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;0.9: strict. Very few wrong matches, but you will miss some real ones.&lt;/li&gt;
&lt;li&gt;0.8: a sensible default.&lt;/li&gt;
&lt;li&gt;0.7: loose. Catches more, and also produces more wrong matches.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Start at 0.8, then sort the matches by score and read the lowest few by eye. If they look wrong, raise the threshold. If real matches are being left blank, lower it. A blank cell is better than a confident wrong answer.&lt;/p&gt;

&lt;h2&gt;
  
  
  FAQ
&lt;/h2&gt;

&lt;h3&gt;
  
  
  Does XLOOKUP help?
&lt;/h3&gt;

&lt;p&gt;Google Sheets has XLOOKUP, but it is an exact-match function. It will not forgive typos unless you pair it with a cleaned key column as in Method 1.&lt;/p&gt;

&lt;h3&gt;
  
  
  Is my data safe?
&lt;/h3&gt;

&lt;p&gt;Methods 1 and 2 are plain formulas. The Apps Script runs under your own account. The Fuzzy Match add-on works inside the spreadsheet you open it in.&lt;/p&gt;

&lt;h3&gt;
  
  
  How big a list can I use?
&lt;/h3&gt;

&lt;p&gt;Plain formulas handle thousands of rows comfortably. The Apps Script version slows down because each row is compared with every master row.&lt;/p&gt;

&lt;p&gt;Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://www.snakbacks.com/fuzzy-match-google-sheets" rel="noopener noreferrer"&gt;https://www.snakbacks.com/fuzzy-match-google-sheets&lt;/a&gt;. Written with AI assistance; every formula on this page was tested in a real Google Sheet.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>appsscript</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Match Names with Typos in Google Sheets (Fuzzy Name Matching)</title>
      <dc:creator>Snakbacks</dc:creator>
      <pubDate>Thu, 19 Nov 2026 07:07:50 +0000</pubDate>
      <link>https://dev.to/snakbacks_sheets/match-names-with-typos-in-google-sheets-fuzzy-name-matching-227a</link>
      <guid>https://dev.to/snakbacks_sheets/match-names-with-typos-in-google-sheets-fuzzy-name-matching-227a</guid>
      <description>&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  The sample data
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 1: clean the obvious differences
&lt;/h2&gt;

&lt;p&gt;Most "typos" are formatting. Fix those first and a normal lookup works.&lt;/p&gt;

&lt;h3&gt;
  
  
  Flip "Last, First"
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(ISNUMBER(SEARCH(",",A2)),TRIM(REGEXREPLACE(A2,"^\s*([^,]+),\s*(.+)$","$2 $1")),TRIM(A2))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"Smith, John" becomes "John Smith". Names without a comma are only trimmed.&lt;/p&gt;

&lt;h3&gt;
  
  
  Strip titles and suffixes
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=TRIM(REGEXREPLACE(A2,"(?i)\b(mr|mrs|ms|miss|dr|jr|sr)\b\.?",""))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"Dr. John Smith Jr" becomes "John Smith". Check your data first: a surname that is also a title word will be removed too.&lt;/p&gt;

&lt;h3&gt;
  
  
  Ignore case, spacing, punctuation and word order
&lt;/h3&gt;

&lt;p&gt;This builds a key by removing punctuation, lowercasing, splitting into words and sorting them:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(A2="","",TEXTJOIN(" ",TRUE,SORT(TRANSPOSE(SPLIT(LOWER(TRIM(REGEXREPLACE(A2,"[^\w\s]","")))," ")))))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"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:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFERROR(INDEX($E$2:$E$100,MATCH(G2,$F$2:$F$100,0)),"")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 2: a looser key for "Jon" versus "John"
&lt;/h2&gt;

&lt;p&gt;Cleaning cannot fix a spelling difference inside a word. A common compromise is a key of first initial plus last word:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(A2="","",LOWER(LEFT(TRIM(A2),1)&amp;amp;" "&amp;amp;REGEXEXTRACT(TRIM(A2),"\S+$")))
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;"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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 3: score-based matching
&lt;/h2&gt;

&lt;p&gt;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: &lt;a href="https://www.snakbacks.com/levenshtein-distance-google-sheets" rel="noopener noreferrer"&gt;https://www.snakbacks.com/levenshtein-distance-google-sheets&lt;/a&gt; or get that formula ready-made in our free starter template: &lt;a href="https://snakback.gumroad.com/l/levenshtein-starter" rel="noopener noreferrer"&gt;https://snakback.gumroad.com/l/levenshtein-starter&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;Another free option is a small Apps Script function (Extensions &amp;gt; Apps Script) that scores a pair using Levenshtein distance, the number of single-character edits between two strings:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight javascript"&gt;&lt;code&gt;&lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nc"&gt;NAMESCORE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="n"&gt;a&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;String&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;toLowerCase&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="n"&gt;b&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;String&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;toLowerCase&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;trim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;!&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt; &lt;span class="o"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="o"&gt;!&lt;/span&gt;&lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="kd"&gt;var&lt;/span&gt; &lt;span class="n"&gt;m&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="n"&gt;cur&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="o"&gt;++&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
      &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;Math&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="n"&gt;prev&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;cur&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
  &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;prev&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="nb"&gt;Math&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;m&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;Two cautions before you trust a cut-off such as 0.85:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;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.&lt;/li&gt;
&lt;li&gt;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.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;(Thanks to a reader on our Hashnode blog for pointing out both of these.)&lt;/p&gt;

&lt;h2&gt;
  
  
  Limits
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  When an add-on or script pack helps
&lt;/h2&gt;

&lt;p&gt;Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.&lt;/p&gt;

&lt;p&gt;Fuzzy Match Template for Google Sheets (US$9, no add-on install): &lt;a href="https://snakback.gumroad.com/l/fuzzy-formulas" rel="noopener noreferrer"&gt;https://snakback.gumroad.com/l/fuzzy-formulas&lt;/a&gt;. 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.&lt;/p&gt;

&lt;p&gt;Our free add-ons Fuzzy Match and Duplicate Remover are coming soon to the Google Workspace Marketplace.&lt;/p&gt;

&lt;p&gt;Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://www.snakbacks.com/match-names-with-typos-google-sheets" rel="noopener noreferrer"&gt;https://www.snakbacks.com/match-names-with-typos-google-sheets&lt;/a&gt;. Written with AI assistance; every formula on this page was tested in a real Google Sheet.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>appsscript</category>
      <category>tutorial</category>
    </item>
  </channel>
</rss>
