Matching Bank Statement Payees to Your Vendor List When the Names Never Agree

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

Your bank feed says CARD PMT ACME TRADING L.L.C 4471. Your vendor master says Acme Trading LLC. XLOOKUP returns #N/A on both, and there are 412 statement lines and 180 vendors waiting. The commodity answer is "use fuzzy matching". The accounting answer is that name matching is the last step, not the first — and that a match you cannot score is a match you cannot sign off.

Post to the wrong vendor and you have not made a spelling mistake. You have moved a balance onto the wrong supplier account, put the spend in the wrong category, and possibly given the payment the wrong VAT or 1099 treatment. Acme Trading LLC and Acme Trading FZE are two different companies with one word between them, and nothing inside the name tells a similarity score which of them took the money.

So this runs in the order a reconciliation actually runs. Narrow the candidates on amount and date, where the answer is exact and cheap. Normalise both name lists into a key and clear the easy hits with an ordinary lookup. Then let a scored fuzzy merge argue only about what is left, and keep the score column in the workbook as the evidence for why you accepted each one. 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.

Do the amount and date first — the name is the tiebreaker

Every guide to this problem starts with the names, because the names are the part that looks broken. Start there and you will spend an afternoon tuning a threshold against 412 lines when perhaps forty of them were ever genuinely in doubt.

Amount and date are exact data. They came off the same payment, they are numbers rather than prose, and nobody at the bank has abbreviated them. Use them to build the candidate set first, and the name comparison is left with a much smaller job: separating the two or three vendors that could plausibly have taken that amount on that day.

On the sheet that is a helper column and a tolerance. Round the amount on both sides so a floating-point tail cannot break an equality test, and allow a window on the date, because a payment leaves the ledger on one day and clears the bank on another. With the statement date in B and the statement amount in C:

=SUMPRODUCT( (ROUND(Ledger[Amount],2)=ROUND($C2,2)) * (ABS(Ledger[Date]-$B2)<=3) )

That counts how many ledger rows are even in the running for this statement line, and the count is worth more than any single match. Zero is a line with no ledger entry behind it at all — a bank charge, a direct debit nobody booked, a missing invoice — and no amount of name matching will invent one. Exactly one is a line you can accept on amount and date alone, with the name used to confirm rather than to decide. More than one is where the name starts doing real work, and it is doing it against a shortlist rather than against the whole vendor master.

In Power Query the same shape is a merge on a rounded-amount key, then a Table.SelectRows filter on the nested result to keep only the rows inside the date window, then the name comparison inside whatever survives. It is worth the extra step for a reason this article gives away further down: Power Query fuzzy matching "is only supported on merge operations over text columns". The amount and the date were never going to be part of a fuzzy join. You either bring them into the decision deliberately, at the front, or you leave them out of it and let a string score choose which supplier gets the money.

What is actually different on a bank line (and what is just bank furniture)

The first four rows, and every one of the bank-furniture rows added below them, are not fuzzy matching problems at all. They are formatting problems in disguise: the bank added that text, the vendor never had it, and it is sitting in the string dragging every similarity score down. They are the cheapest thing to rule out first, and each one you strip out is a threshold point you do not have to spend later.

The last row is the exception and it is worth naming now, because no amount of cleaning helps: when the description names the payment processor, the supplier is not in the string at all. That case has its own section near the end.

Step 1: build a payee key your ledger will recognise

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

Step 1a: delete the bank furniture

Take out everything in the table above that the bank added and the vendor never had — the channel prefix, the trailing card digits, the FX descriptor, the branch or reference block. A SUBSTITUTE chain does it on the sheet; Transform > Replace Values does it in the query, one step per pattern, which is the version that re-runs next month. Keep the raw description in its own column either way, so a reviewer can see what you removed and satisfy themselves that you did not remove half a payee name with it.

Step 1b: 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 1c deletes commas, and after that there is nothing left to detect.

Step 1c: 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 1d: 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 1c 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.

Step 2: find the vendor name buried in the bank narrative

With a clean key on both sides, a large share of the statement clears on an ordinary exact lookup. The next tranche is the lines where the vendor name is genuinely in there, wrapped in narrative the bank wrote — and those do not need a similarity score either.

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.

How many vendors could this line be? Count before you accept

A containment test answers one question: does this vendor name appear in this line? It does not answer the question that decides whether you can post the row, which is whether this is the only vendor it could be. A supplier called Global or Premier or City is inside a quarter of the statement. The formula will hand you whichever one it reaches first, with complete confidence and no score beside it to say how close the runner-up was.

So count the candidates before you look at any of the answers.

=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 how many vendors the line could be:

=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], ""))

Anything with a count above 1 goes to a review queue, not to the ledger. Write that rule into the workbook rather than keeping it in your head, because once the lookup column has filled itself in, a line with three candidates looks exactly like a line with one. 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 rest of this page adds the score — and then the rule for what to do with it.

A note on Microsoft's Fuzzy Lookup Add-In

Microsoft has a free Fuzzy Lookup Add-In for Excel, out of Microsoft Research and BI Labs, still on the 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 returns "a similarity score along with each match" and is robust to "spelling mistakes, abbreviations, synonyms and added/missing data". Uninstall any previous version and run Setup.exe with Run as administrator, which also clears trust-publisher errors; the documentation is the bundled README.docx rather than any Microsoft web page, and marketplace listings with similar names are different products.

The catch is the prerequisite list, which stops at Excel "2007, 2010, 2013, or 2016". Microsoft does not say it fails on anything newer and there is no retirement notice, but nothing newer than 1.3.0.0 exists either. Worth ten minutes if you are curious. Not something to put a monthly close on, and not something that re-runs itself next month, which is why the rest of this page is about Power Query.

Step 3: score what is left with a fuzzy merge

What should reach this step is a short list. The amount-and-date candidate set took out the lines that were never in doubt, Step 1's key cleared the punctuation and suffix differences, and Step 2 caught the names sitting inside a narrative. What is left is the genuine name problem — the typo, the abbreviation, the trading name nobody updated — and this is the only method that puts a number on each one. Standardise on it 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 3a: 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 3b: 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 3c: 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 — the fix on your own side of the file is combining the two columns before the merge.
• 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 3d: 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 3e: add a vendor alias transformation table

A transformation table maps values before the fuzzy algorithm runs, handling what scoring cannot guess. In this job it is a vendor alias table: ACME TRDG to ACME TRADING LTD, Intl to International, the trading name a supplier used before they were bought to the name sitting on your ledger now. Export the vendor list out of your accounting system once, keep it beside the query, and add a row every time you resolve an alias by hand — each one is a line that never comes back to the review queue, and collectively it is the only part of this process that gets better every month. 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.

What score can you actually sign off on?

Here is the question the tutorials never reach, because the tutorials are not posting anything at the end of it. You have a column of similarity scores. Which of them are you allowed to accept without looking?

There is no universal number, and anyone who offers you one has not seen your vendor master. What you can have is a banded policy: decided once, written down, applied the same way every month, and visible to whoever reviews the file.

• Auto-accept only where the amount and the date already agree and the similarity is at or above your top band — 0.95 is a defensible place to put it. Both conditions, not either. A high score on its own is a statement about two strings; the amount and the date are a statement about a payment.
• Review by eye everything from roughly 0.80 up to that line. This is the band where a correct match and a near-identical wrong company sit side by side, and it is where your attention is actually worth something.
• Leave everything below 0.80 unmatched and send it to the queue. A low score is rarely a weak version of the right answer; it is usually a different problem altogether — an acquirer descriptor, a payment nobody booked, a line that needs a person and two minutes.
• Never auto-accept a line above your own posting-error threshold, whatever it scores. A 0.99 on a figure large enough to matter still gets looked at, because the cost of being wrong is not a function of string similarity.

One interaction to know about if you use an alias table: because Microsoft caps transformed matches at 0.95, a row that only matched because of a transformation lands exactly on that boundary rather than above it. That is arguably the right behaviour — it matched on a rule you wrote rather than on the text itself — but decide deliberately whether your top band is 0.95 and above or strictly above 0.95, because the two give different answers on every aliased row.

And the rule the whole thing rests on: never lower the threshold to clear a queue. A threshold that slides from 0.90 to 0.75 on the last afternoon of the close has not matched anything extra. It has stopped telling you which matches were doubtful, which is the only thing it was ever doing for you. If the same aliases keep landing in the review band every month, put them in the transformation table, where the fix is permanent and someone can read it, and leave the threshold where it is.

Then keep the score column in the finished workbook — in the sheet, beside the row, not buried in a query step. "The add-in matched it" is not an answer to a reviewer. A 0.97 in the column next to the line, with the amount and the date agreeing in the columns before it, is. Conditional formatting that bands the column, green from your top band and amber below it, makes a month of matching readable in one scroll and makes an unscored match stand out as the exception it ought to be.

Same name, different entity

Acme Trading LLC and Acme Trading FZE. A group with six companies sharing a stem and one word of difference each. A sole trader and their limited company, both in the vendor master because both have been paid this year. This is where string similarity is at its most confident and its most dangerous at the same time, because the two names genuinely are nearly the same string. The thing that makes them different is not in the text at all.

No threshold fixes this. Raise it and you lose real matches everywhere else; lower it and you have only chosen to be wrong faster. Three things do fix it:

• Use an identifier the name does not carry. Where the statement line shows an account number, sort code or IBAN — common on transfers, rare on card payments — that is an exact key, and it settles the question without an opinion.
• Let the amount and the date do it. Two companies in the same group rarely take the same amount on the same day. The candidate set from the first section will usually contain only one of them, and the near-identical name is then a confirmation rather than a decision.
• Make the alias table disambiguate. Map each real entity to a key of your own — the ledger code, not the trading name — and file the descriptors you actually see on the statement against the right one. A rule you wrote holds. A score you hoped for does not.

Where none of those is available, the line belongs in the review queue with its candidates listed beside it. An unmatched line costs someone five minutes. A payment posted to the wrong company in a group costs an intercompany correction, a restated creditor balance and an awkward conversation about how it was allowed to happen.

Three lines a formula will never match

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.

The acquirer case is the common one, and it has no string answer. A line reading SQ * or PAYPAL * or a bare STRIPE is not a misspelling of your vendor: the description names the company that moved the money, and your supplier is nowhere in it. In the worst version — an acquirer settling a batch — one line on the statement is several payments to several parties, netted, with the processor's fee already taken out, so there is no single vendor for it to name. Normalisation cannot reach through that and neither can a similarity score. What reaches it is the processor's own remittance or settlement report, matched on amount and date; and where the descriptor is stable, a standing mapping from that descriptor to the vendor, kept beside the alias table and reviewed when the wording changes. Build the mapping once and those lines stop arriving in the queue at all.

For those, work a review queue — the Left anti join is that list — or use a matcher that weighs amount, date and description together. The alternative to building that logic yourself is describing the rule in plain English to one of the AI tools that run inside Excel.

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 five accounting systems — QuickBooks Online, Xero, Zoho Books, Odoo and FreshBooks — 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.

Which step does what

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. Cross off each row as you clear it — a conditional-formatting rule that applies strikethrough in Excel marks a reviewed line without deleting it — so nothing in a long queue gets checked twice or skipped.

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 and FreshBooks. The 15-day trial is the full product, no card required.