Fuzzy Match in Excel: Find Names That Nearly Match

A fuzzy match in Excel finds names that are close but not identical, like "Fabrikam, Inc" and "Fabrikam Inc.". Excel has no single fuzzy-match function, but most near matches are really extra spaces, punctuation or extra words, and TRIM, SUBSTITUTE and wildcard lookups handle those. For true spelling mistakes, Power Query has a fuzzy merge. This guide shows each method, from simplest to strongest, so you can match customer, supplier or employee lists that don't line up.

What "fuzzy matching" means#

Fuzzy matching means finding the best match even when two text values aren't exactly the same. A normal lookup like XLOOKUP or VLOOKUP with an exact match fails on any difference at all and returns #N/A. Here are the usual differences and what fixes them:

All the examples use this supplier list in A1:B6:

Step 1: clean the text first#

Cleaning removes the differences that don't matter, so more names match exactly. TRIM deletes spaces at the start and end and squeezes double spaces to one. SUBSTITUTE removes characters you don't care about, like commas and full stops:

=TRIM(SUBSTITUTE(SUBSTITUTE(E2,",",""),".",""))

This turns Fabrikam, Inc. into Fabrikam Inc. Clean both lists the same way, in helper columns, and many "fuzzy" problems simply disappear. If TRIM seems to do nothing, the data may contain non-breaking spaces from a web page; the guide on why TRIM isn't working shows the fix. You can practise cleaning text in a live sheet, and the TRIM function guide has the syntax.

Step 2: find partial matches with XLOOKUP wildcards#

When the typed name is part of the full name, wrap it in wildcards. In Excel, * means "any number of characters", so "*northwind*" matches anything that contains northwind. XLOOKUP uses wildcards only when you set its fifth argument, the match mode, to 2:

=XLOOKUP("*"&TRIM(E2)&"*",A2:A6,B2:B6,"Not found",2)

If E2 holds " northwind ", TRIM gives northwind, the pattern becomes *northwind*, and the result is Lena. Case doesn't matter, so ADVENTURE WORKS returns Omar. XLOOKUP needs Excel 2021 or Microsoft 365. See the XLOOKUP function guide and the article on XLOOKUP wildcard matches for more patterns.

In older Excel, VLOOKUP accepts wildcards whenever the last argument is FALSE:

=VLOOKUP("*"&TRIM(E2)&"*",A2:B6,2,FALSE)

The guide to VLOOKUP with wildcards covers that version. To search for a real * or ? character, put a tilde in front of it: ~*.

Check for more than one match#

A wildcard can match several rows, and XLOOKUP silently returns the first. Before you trust a result, count the candidates:

=COUNTIF(A2:A6,"*"&TRIM(E2)&"*")

A result of 1 is safe. A result above 1 means the search text is too short; "*Works*" might match both "Adventure Works Cycles" and another supplier with "Works" in its name. The wildcards in criteria lesson practises these patterns.

Step 3: when the long name is in your cell#

Sometimes it's the other way round: the table holds short names like Fabrikam, and your cell holds a long one like FABRIKAM INC. (UK). A wildcard around the cell won't help, because the long text isn't inside the short one. Instead, ask SEARCH whether each table name appears inside your cell:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(A2:A6,E2)),B2:B6,"Not found")

SEARCH returns a position number where it finds a table name inside E2, and an error where it doesn't. ISNUMBER turns that into TRUE or FALSE, and XLOOKUP returns the row of the first TRUE. With Fabrikam in the table, the result is Tom. SEARCH ignores case; the SEARCH function guide explains the details.

Step 4: fix typos with Power Query fuzzy merge#

Formulas can't tell that "Contosso" is a misspelling of "Contoso". Power Query, the data tool built into Excel, can. Its Merge Queries command has a fuzzy matching option in Excel for Microsoft 365 on Windows; the Mac and web versions have fewer Power Query features.

1. Turn each list into a table (Ctrl+T), then load each one with Data > From Table/Range.
2. In the Power Query editor, choose Home > Merge Queries.
3. Pick the second table and click the name column in both.
4. Tick Use fuzzy matching to perform the merge.
5. Open Fuzzy matching options and set the Similarity threshold, a number from 0 to 1. Higher is stricter.
6. Click OK, expand the new column, and choose Close & Load.

Fuzzy matching makes guesses, so some will be wrong. Start strict, lower the threshold slowly, and review every match before you use it. Microsoft has also published a Fuzzy Lookup add-in for Windows desktop Excel that works in a similar way.

Which method should you use?#

Start simple and only move on when the simpler method leaves gaps:

If you're matching numbers rather than text, you want the nearest value instead; see how to find the closest match in Excel. And if a lookup still shows #N/A, the guide to fixing the #N/A error walks through every cause.

Practise it#

Wildcard lookups are a favourite in data-cleaning tests. Practise XLOOKUP wildcards and match modes in a live spreadsheet: you search partial names and set the match mode yourself, and each formula is checked instantly. Then try wildcards in criteria and the cleaning text lesson, and do the exercise on this page.