Fuzzy Lookup in Google Sheets: How to Match Similar Text Values (Typos & Variations)

Ever tried to match names or product codes in Google Sheets, only to be frustrated by small typos or formatting differences? That’s where Fuzzy Lookup comes in. Unlike exact matches, fuzzy matching helps you find similar values even when they’re not perfectly typed — think “Jon” vs “John” or “AB-123” vs “AB123”.

This guide will show you how to perform fuzzy lookups in Google Sheets using powerful, built-in and advanced techniques. Whether you’re cleaning up messy data, comparing customer lists, or standardizing entries, fuzzy matching can save you hours of frustration and manual work. And the best part? You don’t need any add-ons or external tools — just formulas!

What Is a Fuzzy Lookup?

A fuzzy lookup matches values that are close but not exactly the same — perfect for cleaning up spelling errors, inconsistent formats, or human mistakes. While Excel needs a separate add-in for this, Google Sheets can do it with smart formulas like REGEXMATCH, FILTER, TEXTDISTANCE (via Apps Script), and more.

Real-Life Scenario: Matching Customer Names with Typos

Let’s say you have a list of customer names exported from two different sources — one from your CRM and one from an online form. They look similar, but not identical.

Source A (CRM)

Customer Name
Jonathan Smith
Emily Jones
Michael Lee

Source B (Web Form)

Customer Name
Jon Smith
Emilee Jones
Michel Lee

Now you want Google Sheets to match these — even with differences in spelling — and flag the closest match.

Step-by-Step Instructions: How to Do a Fuzzy Lookup

Method 1: Using Built-in Functions (No Add-ons)

1. Create your data in two columns: CRM names in Column A and Web Form names in Column B.
2. Use the following formula to compare similarity:

=ARRAYFORMULA(IF(LEN(B2:B), INDEX(A$2:A$4, MATCH(TRUE, REGEXMATCH(LOWER(A$2:A$4), LOWER(B2)), 0)), ""))

This will attempt a partial match between each web form entry and CRM names by comparing the lowercased text.

Method 2: Advanced Matching with Levenshtein Distance (Apps Script)

Levenshtein Distance is a fancy way to say: “How many changes are needed to make one word look like another?”

1. Go to Extensions > Apps Script and paste this code:

function TEXTDISTANCE(a, b) {
  if (!a || !b) return 0;
  a = a.toLowerCase();
  b = b.toLowerCase();
  const matrix = [];
  for (let i = 0; i <= b.length; i++) {
    matrix[i] = [i];
  }
  for (let j = 0; j <= a.length; j++) {
    matrix[0][j] = j;
  }
  for (let i = 1; i <= b.length; i++) {
    for (let j = 1; j <= a.length; j++) {
      if (b.charAt(i - 1) === a.charAt(j - 1)) {
        matrix[i][j] = matrix[i - 1][j - 1];
      } else {
        matrix[i][j] = Math.min(
          matrix[i - 1][j - 1] + 1,
          matrix[i][j - 1] + 1,
          matrix[i - 1][j] + 1
        );
      }
    }
  }
  return matrix[b.length][a.length];
}

1. Save and return to your sheet.
2. Use this formula in cell C2:

=ARRAYFORMULA(MIN(IFERROR(TEXTDISTANCE(B2, A$2:A$4), "")))

This gives you the closest match score. The lower the number, the better the match (0 = perfect).

Sample Output Table

Web Form Name	Closest CRM Match	Similarity Score
Jon Smith	Jonathan Smith	4
Emilee Jones	Emily Jones	2
Michel Lee	Michael Lee	2

Key Benefits of Fuzzy Lookup in Google Sheets

Handles Typos: Fix issues from misspellings or inconsistent naming.
No Add-ons Needed: Unlike Excel, Google Sheets lets you do fuzzy logic with formulas or lightweight scripts.
Flexible Matching: Customize how strict or lenient the match is.
Ideal for Dirty Data: Great for cleaning surveys, leads, user inputs, and imported records.

Pro Tips

Use LOWER() and TRIM() to standardize inputs before matching.
Add a helper column to calculate Levenshtein Distance between strings for ranking.
Combine SORT and FILTER to return the top match with the lowest score.
Set a threshold — e.g., “only accept matches with distance ≤ 3”.

Quick Fuzzy Lookup Cheat Sheet

Function	Use
`REGEXMATCH(text, pattern)`	Match partial or similar strings.
`FILTER(range, condition)`	Filter values by fuzzy condition.
`TEXTDISTANCE(a, b)`	(Custom) Returns the character difference between two strings.
`ARRAYFORMULA()`	Run calculations over entire ranges.

Fuzzy Lookup in Google Sheets is a game-changer for dealing with messy, inconsistent data. Whether you’re matching customer records, deduplicating email lists, or reconciling inventory, you can now do it — even with spelling variations and typos. And with Apps Script, you unlock even smarter fuzzy logic without relying on Excel add-ins.

Try it out, tweak your formulas, and start working smarter — not harder — in Google Sheets!