How to Do Fuzzy Matching in Google Sheets

The Short Answer at a Glance

Fuzzy matching in Google Sheets means finding approximate matches between two columns, such as matching "Acme Corp" against "Acme Corporation" or "Sarah Johnson" against "Sarah Johnston". Google Sheets has no built-in function for this, so you have three realistic paths.

You can approximate it with formulas, normalising text and comparing tokens, but this is fragile and only handles predictable variations.
A short Apps Script function scores every pair for free inside your own account, with the limits described below.
For real fuzzy matching, an add-on like Flookup Data Wrangler compares columns directly and returns the best match with a similarity score.

This guide walks through all three approaches and shows the Flookup workflow step by step.

What Is Fuzzy Matching?

Fuzzy matching compares strings for similarity rather than equality. Two values are a match when they are close enough, even if they are not identical. Typical differences include typos, abbreviations, order changes, missing words and case or punctuation variations.

You need fuzzy matching whenever your data describes the same real-world entity in slightly different ways:

• Matching a supplier list against a master vendor list.
• Linking customer records from two merged databases.
• Finding near-duplicate rows inside one sheet.
• Mapping imported product names to a catalogue.

The output of a fuzzy match is usually a score, such as 0.92 out of 1, that tells you how confident the tool is.
You can set a threshold and only accept matches above it.

Formula-Based Approaches

If your variations are predictable, you can get partway with formulas. The most common tricks:

• LOWER(TRIM(cell)) to neutralise case and trim the surrounding spaces in one step.
• SUBSTITUTE to remove specific punctuation, like hyphens or periods.
• REGEXREPLACE to strip patterns, such as company suffixes or trailing text.
• Comparing first words with LEFT or splitting into tokens to check that key words match.

These work when you know exactly what the variations are. If every company name ends in "Inc" or "LLC" in different forms, you can strip those suffixes and compare the rest.
But the approach breaks down the moment your data contains variations you did not predict, which is the normal case.

A frequent question is whether VLOOKUP can do approximate text matching. It cannot. VLOOKUP matches exactly or, with range lookup, finds the nearest value in sorted numeric order. Neither mode measures text similarity, so misspellings and name variants never match.
A fuzzy VLOOKUP needs a similarity engine, which is what the Flookup add-on provides.

Why Formulas Fall Short

Formula-based matching fails on the exact cases fuzzy matching exists for. A typo like "Johnsone" versus "Johnson" defeats any keyword comparison. Reordered names, "Johnson Sarah" versus "Sarah Johnson", need token-level logic you would have to script.
And similarity scoring, deciding that "Acme Corp" is 90 percent similar to "Acme Corporation", is not something a spreadsheet formula can compute.

For simple, predictable cleaning, formulas are fine.
For genuine fuzzy matching, you need an algorithm that compares strings by similarity and that is what the Flookup add-on provides.

Score Matches Yourself with a Free Script

Google Sheets has no built-in similarity function, but Apps Script adds one. A custom function behaves like any other formula, so it recalculates when the data changes and sits beside the values you are comparing. The lists stay inside your own Google account and never reach a third party.

Open Extensions then Apps Script, delete the placeholder and paste both functions below. Save the project, return to the sheet and type a formula.

function similarity(a, b) {
  const left = String(a).toLowerCase().trim();
  const right = String(b).toLowerCase().trim();
  if (left === right) {
    return 1;
  }
  if (left.length === 0 || right.length === 0) {
    return 0;
  }
  let previous = [];
  for (let j = 0; j <= right.length; j += 1) {
    previous[j] = j;
  }
  for (let i = 1; i <= left.length; i += 1) {
    const current = [i];
    for (let j = 1; j <= right.length; j += 1) {
      const cost = left[i - 1] === right[j - 1] ? 0 : 1;
      current[j] = Math.min(previous[j] + 1, current[j - 1] + 1, previous[j - 1] + cost);
    }
    previous = current;
  }
  const distance = previous[right.length];
  return 1 - distance / Math.max(left.length, right.length);
}
function bestMatch(value, range) {
  let best = "";
  let bestScore = 0;
  for (let i = 0; i < range.length; i += 1) {
    const candidate = range[i][0];
    const score = similarity(value, candidate);
    if (score > bestScore) {
      best = candidate;
      bestScore = score;
    }
  }
  return best + " (" + Math.round(bestScore * 100) + "%)";
}

Both functions are read-only. They do not write to the sheet, so they are safe to leave in a production spreadsheet. There are two ways to use them:

• Score aligned rows: put =similarity(A2, B2) beside the compared columns and fill down. Each row gets a score between zero and one for its own pair.
• Find the best partner: put =bestMatch(A2, $D$2:$D$100) beside the first list to compare every value against the whole second list, for example Jon Smyth (80%).

A threshold turns the scores into a review queue. After similarity, add =IF(C2 >= 0.85, "review", "") and filter on the label. Start around 85 percent and treat the rows near the cut-off as the decisions that deserve a human look rather than an automatic merge.

Where the Free Script Runs Out

The function measures edit distance, the number of single-character edits needed to turn one string into the other. That rewards characters rather than meaning, and the failures follow from it. "Acme Corporation" against "Acme Corp" scores 56 percent because the abbreviation changes the length of the value, "Miriam K." against "Miriam Kamau" scores 67 percent for the same reason, and "John Smith" against "Jon Smyth" lands at 80 percent, below a strict 85 percent cut-off, even though both names describe one person.

Three further limits are worth knowing before you trust a score. Comparisons ignore casing and extra spaces but not punctuation, so "St." against "Street" scores lower than the meaning suggests, so standardise abbreviations before matching. A best-match search does not reserve partners, so two values in the first list can return the same partner, so check for repeated partners before merging. And custom functions time out long before thousands of rows against thousands of values finish, so keep the comparison range as small as the task allows.

Lowering the threshold catches the abbreviations but floods the list with pairs that merely share letters, so no single cut-off separates every real match from every false one. Treat the pairs just above and just below the line as the review queue. When matching has to hold across whole datasets, the next section picks up from there.

Fuzzy Matching with the Flookup Add-on

Flookup Data Wrangler is a Google Sheets add-on available from the Google Workspace Marketplace. It adds three tools you can use for fuzzy matching:

• Fuzzy Match: compare a search column against a reference column and return the closest match with a similarity score.
• Smart Deduplicate: find near-duplicate groups inside a single column or range.
• Text Standardisation: clean up casing, punctuation and formatting before you match.

Fuzzy matching runs inside the spreadsheet, with no formula to write and no scripting. The documentation shows it working on sample data before you buy.

Step-by-Step, Match Two Columns

Here is how to fuzzy match two columns with Flookup:

1. Install the add-on. Open Extensions > Add-ons > Get add-ons, search for Flookup and install it.
2. Open Fuzzy Match. From the Flookup sidebar, choose the Fuzzy Match tool.
3. Select your search column. This is the column of values you want to look up, for example your incoming supplier list.
4. Select your reference column. This is the master list you want to match against.
5. Run the match. Flookup returns the closest match for each row plus a similarity score.
6. Review confidence. Filter for low scores and decide whether to accept, adjust the threshold or review manually.

The similarity score is the key to control. Set a high threshold for conservative matching or a lower one for aggressive recall, then review the edge cases.

Fuzzy Deduplication in Sheets

Fuzzy matching is also how you find duplicate rows inside one sheet. Instead of comparing two columns, you look for rows that describe the same entity.

Flookup's Smart Deduplicate runs four detection strategies, exact case-insensitive matching, punctuation normalisation, phonetic similarity and fuzzy percentage matching and groups results ranked by confidence. How to Remove Duplicates in Google Sheets covers the full workflow. Formula syntax lives in Standard Data Cleaning, and for recurring runs see Schedule Functions.

Formulas and Reusable Recipes Without Rewriting Them

QUERY filters, REGEXREPLACE normalises, IMPORTRANGE pulls the range, and Named Functions save the recipe under a short name. Build once, then run Flookup fuzzy match on the prepared columns and save the whole pass as a reusable cleaning template. Get started in Sheets.

Four Detection Strategies for Removing Duplicates in Google Sheets

Fuzzy matching in Google Sheets is easy once you stop trying to build it from formulas. Install the add-on, pick your columns and review the similarity scores.
The matching runs in seconds and you keep full control over which results you accept.

For a deeper look at how the underlying algorithms work, see fuzzy matching algorithms explained.

Frequently Asked Questions