How to Fuzzy Match in Excel When Names Don't Match Exactly

How-to · Updated July 2026 · ~13 min read

Your bank statement export says CARD PMT ACME TRADING L.L.C 4471. Your supplier master says Acme Trading LLC. XLOOKUP returns #N/A, and so does every exact-match formula you try. Same story with ABC Corp versus ABC Corporation, or a trailing space nobody can see. Exact matching is a binary test on strings a human reads as identical.

Below are four approaches, in increasing order of power. The free bank reconciliation template gives you somewhere to put the results: a Reconciliation sheet with bank-side and book-side adjustments and a DIFFERENCE row printing "RECONCILED" or "OUT BY", plus Bank statement and Ledger sheets with a Matched? column across 200 rows. Its limitation is the gap this article fills — that column compares amounts only, never descriptions. For the wider cleanup, see cleaning messy data in Excel and removing duplicates in Excel.

First, classify what is actually different

The first four rows are not fuzzy matching problems at all. They are formatting problems in disguise, and they are the cheapest thing to rule out first.

Method 1: normalise both lists first

Build a cleaned key in helper columns on each list, then run an ordinary exact-match lookup on the keys. Order matters: flip reversed names while the comma is still there, then strip punctuation, then standardise suffixes.

Step 1: flip reversed names (column A into B)

=IF(ISNUMBER(SEARCH(",",A2)),
   TRIM(MID(A2,SEARCH(",",A2)+1,255)) & " " & TRIM(LEFT(A2,SEARCH(",",A2)-1)),
   A2)

SEARCH returns #VALUE! when the text is absent, so the ISNUMBER wrapper is what keeps this safe on rows with no comma. Do it first — Step 2 deletes commas, and after that there is nothing left to detect.

Step 2: strip punctuation, invisible spaces and case (B into C)

=UPPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,".",""),",",""),"-"," "),"&"," AND "),CHAR(160)," ")))

From the inside out: delete full stops and commas, hyphens to spaces, & to AND (spaces both sides, or A&B becomes A ANDB), then CHAR(160) non-breaking spaces — the commonest invisible culprit in text copied out of a portal or a PDF — to ordinary ones. TRIM wraps the lot rather than sitting inside it, because those substitutions leave double spaces behind and TRIM collapses internal runs as well as the ends. Add CLEAN if line breaks are in play.

Step 3: standardise legal suffixes (C into D)

=LET(
  padded,  " " & C2 & " ",
  fixLtd,  SUBSTITUTE(padded,  " LIMITED ",      " LTD "),
  fixInc,  SUBSTITUTE(fixLtd,  " INCORPORATED ", " INC "),
  fixCorp, SUBSTITUTE(fixInc,  " CORPORATION ",  " CORP "),
  fixCo,   SUBSTITUTE(fixCorp, " COMPANY ",      " CO "),
  TRIM(fixCo)
)

The search terms are uppercase because Step 2 uppercased the text. Padding the string with a space at each end makes every substitution fire on whole words only — without it, SUBSTITUTE(x,"LIMITED","LTD") turns UNLIMITED SOFTWARE into UNLTD SOFTWARE. And LET names must not look like cell references, hence fixLtd: Excel rejects s1, which parses as column S, row 1. LET needs Microsoft 365, 2021 or 2024; on 2016 or 2019, nest the SUBSTITUTE calls instead.

Now run your exact-match lookup against the column-D keys on both sides. Whatever is still #N/A is a genuine fuzzy problem — usually a much shorter list than you feared.

Method 2: wildcards, SEARCH and containment tests

When one string sits inside the other, wildcards handle it with no add-in — provided you tell XLOOKUP to allow them. match_mode must be 2, "A wildcard match where *, ?, and ~ have special meaning". The default 0 is "Exact match. If none found, return #N/A." Because match_mode is the fifth argument of XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]), you have to supply if_not_found to reach it:

=XLOOKUP("*"&$A2&"*", Suppliers[Name], Suppliers[Code], "Not found", 2)

? matches any single character; ~ escapes a literal * or ?.

XLOOKUP is in Excel for Microsoft 365, 2021 and 2024 (Windows and Mac) plus web and mobile, and not in Excel 2016 or 2019. There, fall back to INDEX/MATCH, where match type 0 takes the same wildcards:

=INDEX($E$2:$E$500, MATCH("*"&$A2&"*", $D$2:$D$500, 0))

The reversed case: a long bank narrative

Wildcards only wrap the value you search for. The accounting case is often the reverse — a long bank description with one of several hundred supplier names buried inside it:

=XLOOKUP(TRUE, ISNUMBER(SEARCH(Suppliers[Name], $A2)), Suppliers[Code], "Not found")

SEARCH(find_text, within_text, [start_num]) runs against every supplier name at once, ISNUMBER turns positions and #VALUE! misses alike into TRUE/FALSE, and XLOOKUP returns the code for the first TRUE. Needs dynamic arrays: Microsoft 365, 2021 or 2024. Note that SEARCH, not FIND, is the right tool on names typed by different people — Microsoft's line is "FIND is case-sensitive, and SEARCH is not case-sensitive" — and it takes ? and * in find_text too.

Flag the ambiguous ones before you trust any of it

=IF($D2="","",IF(ISNUMBER(SEARCH($D2,$A2)),"match",""))

The outer IF matters: given an empty $D2, SEARCH returns 1 rather than an error, so blank reference rows would otherwise all report a match. Then count the candidates, because a supplier called Global will match half your statement:

=SUMPRODUCT(--ISNUMBER(SEARCH(Suppliers[Name], $A2)))

Anything above 1 needs a human. To see which ones — TEXTJOIN needs Excel 2019 or later, confirmed with Ctrl+Shift+Enter before Microsoft 365:

=TEXTJOIN(" | ", TRUE, IF(ISNUMBER(SEARCH(Suppliers[Name], $A2)), Suppliers[Name], ""))

Ambiguity is the ceiling on formula matching: a formula hands you a confident wrong answer with no score telling you how much to trust it. The next two methods add the score.

Method 3: Microsoft's free Fuzzy Lookup Add-In

The Fuzzy Lookup Add-In for Excel is a real Microsoft tool that many people do not know exists. It came out of Microsoft Research and BI Labs and is still a free download from the Microsoft Download Center: microsoft.com/en-us/download/details.aspx?id=15011 — version 1.3.0.0, a single Setup.exe of 1.4 MB. It "performs fuzzy matching of textual data in Microsoft Excel", returns "a similarity score along with each match", and is robust to "spelling mistakes, abbreviations, synonyms and added/missing data".

Prerequisites, as Microsoft lists them: Excel "2007, 2010, 2013, or 2016"; ".NET 4.5"; "VSTO 4.0"; "1 GB of RAM"; "2 GB of available hard disk space".

That Excel list is the catch: 2019, 2021, 2024 and Microsoft 365 are not on it. Microsoft does not say it fails on them either, and there is no retirement notice — but nothing newer than 1.3.0.0 exists, and the page says nothing about 32-bit versus 64-bit Excel. Worth ten minutes to try; not something to build a monthly process on.

Installing: uninstall any previous version, then run Setup.exe (right-click, Run as administrator for a per-machine install, which also fixes trust-publisher errors). Work from the bundled README.docx, because none of the add-in's in-Excel UI is documented on any Microsoft web page. Marketplace listings with similar names are different products.

Method 4: Power Query fuzzy merge — the one to build on

Standardise on this if your Excel supports it: refresh next month and the whole match re-runs.

Version gate first. Microsoft states plainly: "Fuzzy merge is supported in Microsoft 365, but not in 2019 perpetual." Power Query itself has been in Excel since 2016, but fuzzy merge is not in the perpetual subset. Only 2019 perpetual is named, so treat 2021 and 2024 perpetual as unconfirmed until you open the dialog and look.

Step 1: load both tables as queries

Select each table and use Data tab > Get & Transform Data group > From Table/Range. New to the editor? Start with Power Query for beginners.

Step 2: merge them

Merging happens inside the Power Query Editor, not on Excel's Data tab: Home tab > Combine group > Merge queries. The dropdown also offers Merge queries as new, which preselects no tables and builds a third query.

1. Set Left table for merge to your messy data and Right table for merge to your clean reference list — Microsoft's own walkthrough uses that orientation.
2. Click the key column in each table to pair them. For multi-column joins, Ctrl-click the extras; the selection order must match between tables.
3. Set Join kind to Left outer so every messy row survives. The others are Right outer, Full outer, Inner, Left anti and Right anti — Left anti, as a second query, gives you only the failures.
4. Tick Use fuzzy matching to perform the merge at the bottom.

The footer then estimates the result — "The selection matches 8 of 9 rows from the first table" — a fast sanity check.

Step 3: expand Fuzzy matching options

• Similarity threshold (optional) — "A value between 0.00 and 1.00 that provides the ability to match records above a given similarity score." Default 0.80; 1.00 "is the same as specifying an exact match criteria".
• Ignore case — "Allows matching records no matter what the case of the text".
• Match by combining text parts — "Micro soft is matched with Microsoft if this option is enabled". Useful after a bad column split.
• Show similarity scores — turn this on, always. It is the difference between an auditable match and a guess.
• Number of matches (optional) — maximum matching rows per input row. Set it to 1 unless you want alternatives.
• Transformation table (optional) — see below.

Click OK. A nested column named after the right table appears; click its expand icon, pick the field you want plus Similarity score, and untick Use original column name as prefix. Unmatched rows expand to null.

Two constraints: fuzzy matching "is only supported on merge operations over text columns", and the algorithm is Jaccard similarity, not Levenshtein or Jaro-Winkler.

Step 4: tune the threshold with evidence

0.80 is a starting point, not an answer. Grapes matches Graes "only if the threshold is set to less than 0.90". And long free text scores badly, because the name you care about is only a fraction of the string — in one Microsoft example the threshold had to come down to 0.5 before My favorite fruit, by far, is Apples. I simply love them! grouped with Apples at all. So for bank descriptions: strip the surrounding noise first, via Transform > Replace Values or Extract, or lower the threshold and read the scores. Clean extraction helps if the statement began as a PDF — see converting a bank statement PDF to Excel.

Step 5: add a transformation table for abbreviations

A transformation table maps values before the fuzzy algorithm runs, handling what scoring cannot guess: Intl to International, an old trading name to the current one. Build it as its own query with exactly two columns, headers exactly From and To — "From contains the values to find. To contains the values that are used to replace the values found by using the From column." Microsoft's example is a single row: From apls, To Apple. Pick that query in the dropdown.

Two caveats worth pinning to the wall.

Scores cap at 0.95. "When the transformation table is used, the maximum similarity score for the values from the transformation table is 0.95" — a deliberate 0.05 penalty marking that a transformation occurred. If you need clean 1.00 scores, Microsoft's workaround is Replace Values on the column first, then fuzzy merge.

It applies everywhere. "Note that the transformation will be applied to all occurrences of the text in the transformation table." With From Grapes and To Raisins, Grapes are sweet also matches Raisins are sweet. Keep From values long and specific.

One thing not to look for in Excel: Cluster values. Tutorials mention it, but Microsoft states it "is only available in Power Query Online".

The M code, if you would rather type it

= Table.FuzzyNestedJoin(
    BankLines,  {"Description"},
    Suppliers,  {"SupplierName"},
    "Match",
    JoinKind.LeftOuter,
    [
        IgnoreCase           = true,
        IgnoreSpace          = true,
        NumberOfMatches      = 1,
        SimilarityColumnName = "Similarity score",
        Threshold            = 0.75,
        TransformationTable  = SupplierAliases
    ]
)

Documented defaults if you omit them: IgnoreCase and IgnoreSpace true, Threshold 0.80, all matches returned, no score column, left outer join.

When to stop using formulas

Some matches are not recoverable from string similarity at all, and recognising that beats dropping the threshold to 0.4:

• The description contains no supplier name, only a payment processor or card acquirer.
• One deposit covers several invoices, so you need amount-and-date logic, not name logic.
• Two entities have near-identical names and only the amount or account separates them.

For those, work a review queue — the Left anti join is that list — or use a matcher that weighs amount, date and description together.

Where HISAB 360 fits

HISAB 360 is a paid Excel add-in with an AI chat panel docked inside Excel; it writes and applies real Excel artefacts to your live workbook rather than handing you text to copy. Here that means asking for the match in plain English — allow for punctuation and Ltd/Limited differences, show a confidence score — and getting the normalisation steps and the fuzzy merge back as a query you can open and edit.

Its bank reconciliation feature is the case this article has circled: it matches statement lines to ledger rows on amount and date tolerance plus fuzzy description matching, handles many-to-one deposits, flags duplicates and produces a reconciliation report. It reads and writes six accounting systems — QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage — so a matched name can be checked against the live vendor master.

Honest limits: Windows desktop Excel only, not Mac and not Excel on the web. It is paid — a 15-day free trial of every feature with 50 HISAB AI credits, no card required, once per machine, then a subscription or perpetual licence (pricing). Connecting an accounting system needs your own OAuth authorisation. And a fuzzy match is still fuzzy: review the matches and the scores, and never post a write back to a ledger unchecked.

Choosing between the four methods

The practical recipe: normalise, exact match, then send only the leftovers through a fuzzy merge with Show similarity scores on and Number of matches set to 1, and review anything below about 0.9 by eye.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.