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.
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.
Master list (columns D and E):
Initech, 101
Umbrella Corp, 102
Acme Pty Ltd, 103
Globex Corporation, 104
Messy names (column A, starting at A2):
ACME Pty. Ltd
Inytech
Corp Umbrella
Globex
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.
This handles case, extra spaces and punctuation. It does not handle typos or word order.
In F2 on the master list, build a clean key and fill it down:
=LOWER(TRIM(REGEXREPLACE(D2,"[^\w\s]","")))
Then in B2, clean the messy name the same way and look it up against the key column:
=IFERROR(INDEX($E$2:$E$100, MATCH(LOWER(TRIM(REGEXREPLACE(A2,"[^\w\s]",""))), $F$2:$F$100, 0)), "")
"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.
If your spreadsheet uses a comma as the decimal mark, Sheets may want semicolons between arguments instead of commas.
Use this when one name is a shortened version of the other.
=IFERROR(INDEX($E$2:$E$100, MATCH("*"&A2&"*", $D$2:$D$100, 0)), "")
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.
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:
=SUMPRODUCT(--ISNUMBER(SEARCH(SPLIT(LOWER(REGEXREPLACE(TRIM($G$1),"[^\w\s]",""))," "),LOWER(D2))))
Then pick the highest score:
=IF(MAX(H2:H100)=0,"",INDEX(D2:D100,MATCH(MAX(H2:H100),H2:H100,0)))
"Corp Umbrella" scores 2 against Umbrella Corp, 1 against Globex Corporation and 0 against the rest, so it returns Umbrella Corp.
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.
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.
Open Extensions > Apps Script, paste this, and save:
function CLOSEST_MATCH(value, list, minScore) {
var target = norm_(value);
var min = (minScore === undefined || minScore === "") ? 0.8 : minScore;
var items = [].concat.apply([], list);
var best = "", bestScore = 0;
for (var i = 0; i < items.length; i++) {
var cand = norm_(items[i]);
if (!cand) continue;
var score = 1 - lev_(target, cand) / Math.max(target.length, cand.length);
if (score > bestScore) { bestScore = score; best = items[i]; }
}
return bestScore >= min ? best : "";
}
function norm_(s) {
return String(s).toLowerCase().replace(/[^\w\s]/g, "").replace(/\s+/g, " ").trim();
}
function lev_(a, b) {
var prev = [], i, j;
for (j = 0; j <= b.length; j++) prev[j] = j;
for (i = 1; i <= a.length; i++) {
var cur = [i];
for (j = 1; j <= b.length; j++) {
var cost = a.charAt(i - 1) === b.charAt(j - 1) ? 0 : 1;
cur[j] = Math.min(prev[j] + 1, cur[j - 1] + 1, prev[j - 1] + cost);
}
prev = cur;
}
return prev[b.length];
}
Use it in a cell, one row at a time, and fill down:
=CLOSEST_MATCH(A2, $D$2:$D$100, 0.8)
"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.
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:
=FUZZYMATCH(A2:A100, Customers!A2:A, 0.8)
=FUZZYLOOKUP(A2:A100, Customers!A2:A, Customers!C2:C, 0.8)
=FUZZYSCORE(A2, B2)
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.
It is coming soon to the Google Workspace Marketplace.
If you cannot install add-ons, or prefer to own the code: Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas. It is the same three formulas as Apps Script you paste in once, or get by copying a template sheet. Snakbacks sells this pack.
Only case, spaces or punctuation differ: Method 1. Quick and exact.
Names are shortened versions of each other: Method 2, wildcard.
Word order differs: Method 2 word overlap, or the add-on.
Typos: Method 3, either option.
Thousands of rows, repeated often: the add-on, since the formulas fill a whole column in one go.
Fuzzy matching returns a score from 0 (nothing alike) to 1 (identical). You decide the cut-off:
0.9: strict. Very few wrong matches, but you will miss some real ones.
0.8: a sensible default.
0.7: loose. Catches more, and also produces more wrong matches.
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.
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.
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.
Plain formulas handle thousands of rows comfortably. The Apps Script version slows down because each row is compared with every master row.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.