Match Bank Transactions in Excel with XLOOKUP

TL;DR To match bank transactions in Excel, put the bank statement and your ledger (or invoice list, or check register) in two tables, then look up each bank line in the ledger. XLOOKUP on a single column works when values are unique, like check numbers or references. For amounts, which repeat, build a match key that adds an occurrence number (amount|1, amount|2) so each bank line pairs with exactly one ledger line. Add a date tolerance with COUNTIFS or FILTER, and finish with two lists: bank items not in the ledger, and ledger items not in the bank. Those lists are your reconciliation.

Matching is the heart of reconciliation. Whether you're tying a bank statement to your accounting records, checking which invoices have been paid, comparing a card statement with receipts, or confirming that payment processor payouts arrived, the job is the same: pair up items in two lists and see what's left over.

Done by eye, it's slow and error-prone. Done naively with a lookup on amount alone, it gives wrong answers as soon as two transactions share an amount, which happens all the time with rent, subscriptions and payroll. This guide builds up from the simplest match to one that handles repeated amounts, date differences and many-to-one payments. The formulas use Excel 365 functions such as XLOOKUP and FILTER; we note alternatives for older versions.

Set up two clean tables

Everything depends on clean input.

Bank table (Bank): one row per statement transaction, with Date, Description, Check #, and a signed Amount (money in positive, money out negative). If you're starting from a PDF statement, convert it first; SheetStatement exports Excel or CSV with real dates, signed amounts and a balance check. The free plan covers 3 pages a month, with Starter ($9), Pro ($19) and Business ($39) monthly plans for more.

Ledger table (Ledger): the other side, such as an export of the bank account from your accounting software, an invoice list, or a check register. Same idea: Date, Description, Reference, and a signed Amount using the same sign convention as the bank.

Before matching:

• Prove the bank table is complete: opening balance plus SUM(Bank[Amount]) should equal the closing balance. The free balance checker does it without formulas. Matching against an incomplete statement just produces false "unmatched" items.
• Make amounts numbers, not text, and round them to cents with ROUND if they came from calculations. A value of 100.0000001 won't match 100.
• Make dates real dates. Our guide to fixing bank statement dates in Excel covers text dates and locale problems.
• Use the same sign convention on both sides. If your ledger shows payments as positive, flip them.

Level 1: match on a unique reference

When both sides share a unique identifier, matching is easy. Check numbers are the classic example.

In the Bank table, add a column:

=XLOOKUP([@[Check #]], Ledger[Reference], Ledger[Amount], "Not in ledger")

This returns the ledger amount for the same check number. Add a second column to compare:

=IF(ISNUMBER([@LedgerAmount]), IF(ROUND([@Amount]-[@LedgerAmount],2)=0, "Match", "Amount differs"), "Not in ledger")

Now you have three outcomes: matched, matched by number but with a different amount (often a typo), and missing from the ledger. Do the reverse lookup in the Ledger table to find checks you've recorded that haven't cleared:

=IF(ISNA(XMATCH([@Reference], Bank[Check #])), "Outstanding", "Cleared")

In older Excel, INDEX/MATCH or VLOOKUP with exact match does the same job.

Level 2: match on amount, the naive way

Most bank lines don't have a reference. Card purchases, transfers and direct debits only have a description and an amount. The obvious move is to look up the amount:

=XLOOKUP([@Amount], Ledger[Amount], Ledger[Date], "No match")

This works until two transactions share an amount. If you pay 49.00 for software twice in a month, both bank lines find the first 49.00 ledger line. The second ledger line never gets matched, and your report says everything is fine when it isn't. You need each match to be used once.

Level 3: a match key with occurrence numbers

The fix is to number each occurrence of an amount on each side, then match amount and occurrence together.

In the Bank table, add an Occurrence column. With rows in date order:

=COUNTIF(INDEX([Amount],1):[@Amount], [@Amount])

This counts how many times the current amount has appeared so far, including the current row: 1 for the first 49.00, 2 for the second, and so on. (The INDEX([Amount],1):[@Amount] part builds an expanding range inside a table.)

Then a Key column:

=TEXT([@Amount],"0.00") & "|" & [@Occurrence]

Do exactly the same in the Ledger table. Now match on the key:

=XLOOKUP([@Key], Ledger[Key], Ledger[Date], "Unmatched")

The first 49.00 on the bank matches the first 49.00 in the ledger, the second matches the second. Each ledger line is used once.

This works well when both lists are in date order and the same transactions appear in the same sequence. It can pair the wrong two items if one side has a 49.00 the other side lacks, early in the month. For most monthly reconciliations that's acceptable: the totals still come out right, and the leftover items show you what to investigate. When you need precision, add dates, as in the next level.

Level 4: amount plus a date window

Banks post transactions a day or more after you record them. A check written on the 28th clears on the 3rd. A card purchase on Saturday posts Monday. So exact date matching misses real pairs, and amount-only matching can pair the wrong ones. Combine them: same amount, within a few days.

To test whether a match exists:

=COUNTIFS(Ledger[Amount], [@Amount], Ledger[Date], ">="&[@Date]-5, Ledger[Date], "<="&[@Date]+5)

0 means nothing in the ledger has that amount within 5 days either side. 1 means a clean match. More than 1 means ambiguous; look at it by hand or tighten the window.

To return the matching ledger line, use FILTER and take the first:

=TAKE(FILTER(Ledger[Description], (Ledger[Amount]=[@Amount]) * (ABS(Ledger[Date]-[@Date])<=5), "Unmatched"), 1)

TAKE is in current Excel 365. In versions without it, wrap FILTER in INDEX(..., 1).

For a strict one-to-one match with dates, combine the occurrence idea with the window: number occurrences of each amount on each side, and require the dates to be within the window as a check after the key match:

=LET(d, XLOOKUP([@Key], Ledger[Key], Ledger[Date], ""), IF(d="", "Unmatched", IF(ABS(d-[@Date])<=5, "Match", "Check date")))

"Check date" flags pairs where the key matched but the dates are far apart, a hint that the occurrence numbering paired the wrong items.

Level 5: one bank line, several ledger lines

Some bank lines represent several items in your records:

• A deposit containing several customer payments.
• A card processor payout covering many sales, net of fees.
• A payroll debit covering many employees.

Lookups can't match one to many. Instead, give the ledger lines a Group value that ties them together (a deposit slip number, a payout ID, a payroll run date), then sum the group:

=SUMIFS(Ledger[Amount], Ledger[Group], [@Group])

and compare that sum with the bank line. For processor payouts, the processor's payout report is the place to get the group; our guide to reconciling Stripe payouts shows the full method.

If you don't have a group ID, finding which combination of ledger items adds up to a deposit is a harder problem. For a handful of candidates, sorting by date and adding running totals usually finds it. For larger sets, Excel's Solver can search combinations, but it's slow and can find coincidental matches, so keep deposit records that make it unnecessary.

Build the exception lists

The point of matching is the leftovers. Use FILTER to list them on a summary sheet:

Bank items not in the ledger:

=FILTER(Bank, Bank[Status]="Unmatched", "None")

These are typically fees, interest, automatic payments, and anything nobody recorded yet. Record them in your books, or investigate if you don't recognize them.

Ledger items not in the bank:

=FILTER(Ledger, Ledger[Status]="Unmatched", "None")

These are typically outstanding checks and deposits in transit, which belong on your reconciliation. Anything older than a month or two deserves a closer look. Our guide to outstanding checks and deposits in transit covers what to do.

Then tie it together:

The full reconciliation template is in our guide to reconciling a bank statement in Excel.

Matching on descriptions

Amounts are the most reliable match field, but descriptions help when amounts are close or ambiguous. Bank descriptions are messy (CARD PURCHASE 0912 CLOUDHOST*INV 8812 SAN FRAN), while your ledger might say "Cloud hosting". Exact matching won't work, but keyword checks can:

=ISNUMBER(SEARCH("CLOUDHOST", [@Description]))

A small rules table that maps keywords to vendors, combined with amount matching, resolves most ambiguous pairs. Our guide to Excel formulas to categorize transactions shows how to build that table. Power Query's fuzzy merge is another option for messy text, worth trying when you match large lists regularly.

A worked example

A small agency reconciles its operating account for September. The bank statement has 142 lines; the ledger export has 139.

1. Prep. The statement is converted, the balance check is zero, and both tables use negative for money out.
2. Checks. Eleven checks are matched by number with XLOOKUP. One shows "Amount differs": 1,540.00 in the bank, 1,450.00 in the ledger. The difference is 90, divisible by 9, a transposition. The ledger entry is corrected.
3. Keys. Occurrence keys are built on both sides. Three monthly software charges at 29.00 match one-to-one.
4. Date window. A 5-day window flags two "Check date" items: a 250.00 transfer matched to one 25 days away. The real ledger entry for that transfer was missing; the key paired it with an unrelated 250.00. Recording the missing transfer fixes both.
5. Payouts. Four card processor payouts are matched to grouped sales using payout IDs.
6. Exceptions. Bank items not in the ledger: a monthly fee and an interest credit, both recorded. Ledger items not in the bank: two checks written on the 29th and 30th, now on the outstanding list.
7. Tie-out. The adjusted bank balance equals the ledger balance.

Common matching mistakes

Floating-point noise. Amounts produced by formulas (for example a VAT split or a currency conversion) can carry tiny fractions that don't show on screen. 120.00 and 119.99999999 look identical and won't match. Wrap calculated amounts in ROUND(..., 2) on both sides, and build keys with TEXT(amount, "0.00") as shown above.

Mixed sign conventions. A ledger export that shows withdrawals as positive numbers in a "Payment" column won't match bank lines that show them as negative. Convert both sides to one signed column before anything else. Our guide to separating debit and credit columns covers the conversion.

Sorting after building occurrence numbers. The occurrence formula depends on row order. If you sort the table afterwards, the numbers no longer mean "first, second, third in date order". Sort by date first, then build the occurrence column, or convert the numbers to values before re-sorting.

Matching across months. A December check that clears in January appears in December's ledger and January's statement. If you match one month at a time, it shows as outstanding in December and as a bank item not in the ledger in January. Either carry the December outstanding list forward into January's matching, or include the last few days of the previous month's ledger in each month's match.

Ignoring duplicates on one side. If the ledger contains the same transaction twice, perhaps from an import that overlapped a bank feed, the duplicate shows up as an unmatched ledger item. Don't put it on the outstanding list; delete it. Our guide to duplicate transactions after import covers the cleanup in QuickBooks.

Matching a card statement to receipts

The same technique works for credit card statements against a receipts log. Put the card statement in one table and your receipts in another, with amounts in the same sign. Match with a key and a date window, because receipts are dated when you bought something and card lines are dated when they posted. Unmatched card lines are missing receipts; unmatched receipts are either pending charges or receipts filed against the wrong card. Our guide to credit card reconciliation in Excel has a full template.

Foreign currency purchases need care: the receipt shows the foreign amount and the card shows the converted amount. Match those by date and merchant instead of amount, or record the converted amount on the receipt once the statement arrives.

Make it reusable

Once the workbook works, keep it as a template:

• Paste new data into the tables; formulas extend automatically.
• Keep the window size in one cell and reference it, so you can widen it when needed.
• Keep a log of differences resolved each month. Patterns, like a vendor whose charges always post late, help next time.
• Use Power Query to load the bank and ledger files from a folder, so refreshing is one click. See our Power Query bank statement guide.

The same workbook works in Google Sheets with small changes. XLOOKUP and FILTER exist there too; see our guide to reconciling a bank statement in Google Sheets.

FAQ

What's the best Excel formula to match bank transactions?

XLOOKUP for unique values like check numbers. For amounts, which repeat, add an occurrence number to build a unique key on each side, then use XLOOKUP on the key. Add a date window with COUNTIFS or FILTER for accuracy.

Why does my lookup match the same ledger line twice?

A plain lookup always returns the first match. When two transactions share an amount, both find the same line. Number the occurrences of each amount on both sides and match on amount plus occurrence.

How do I allow for posting delays?

Compare dates within a window, such as 3 to 5 days, using COUNTIFS with greater-than-or-equal and less-than-or-equal date criteria, or FILTER with an ABS date difference.

How do I match one deposit to several invoices?

Give the related ledger lines a group value, such as a deposit slip or payout ID, and compare the bank line with SUMIFS of the group.

Does this work without Excel 365?

Yes, with adjustments. Use INDEX/MATCH instead of XLOOKUP, and helper columns instead of FILTER and LET.

Do I need to convert my PDF statement first?

Yes. Matching needs the bank statement as a table with real dates and numeric amounts. Convert the PDF and confirm it balances before matching.

What should I do with unmatched items?

Bank items not in your books usually need recording, such as fees and interest. Book items not in the bank are usually outstanding checks or deposits in transit. Investigate anything you can't explain.

Why do matched amounts still show as different?

Usually hidden decimals from calculations, or text that looks like a number. Round both sides to two decimals and convert text to numbers before matching.

Can I match more than one month at a time?

Yes, but carry outstanding items forward and allow for transactions that cross month-end. Matching one month at a time, with last month's outstanding list included, is usually easier to review.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro $19 and Business $39.