Matching is the slow part of a bank reconciliation. A few well-chosen formulas do most of it.
Paste the bank statement and the cash book onto two sheets with one sign convention: money in positive, money out negative. Build a matching key from the amount and how many times that amount has appeared, so repeated amounts pair one-to-one. Look each bank key up in the ledger, check near-dates for the rest, total batched deposits with SUMIFS, and list whatever is left as reconciling items.
Bank sheet: A Date, B Description, C Amount. Ledger sheet (the cash book for that bank account): A Date, B Reference, C Amount, E Batch reference if you bank receipts together.
Use one sign convention on both sheets: money in positive, money out negative. If your ledger export shows debits and credits in separate columns, create the signed amount first:
=ROUND(Debit-Credit,2)
Clean dates and amounts before you start. Text that looks like a date won't match; see converting text to dates and fixing rounding differences.
Matching on amount alone fails when the same amount appears more than once, such as two payments of 250.00. Give each occurrence a number. In column D on both sheets:
=TEXT(C2,"0.00")&"|"&COUNTIF(C$2:C2,C2)
The first 250.00 becomes "250.00|1", the second "250.00|2". Each key is unique, so each bank line can only pair with one ledger line. Sort both sheets by date first so occurrences pair in date order.
On the Bank sheet, column E:
=XLOOKUP(D2,Ledger!D:D,Ledger!B:B,"Unmatched")
This returns the ledger reference for a match, or "Unmatched". Add the reverse lookup in column F of the Ledger sheet so you can see unmatched items on both sides:
=XLOOKUP(D2,Bank!D:D,Bank!B:B,"Unmatched")
Then check the dates of matched pairs in column F of the Bank sheet. A pair more than a few days apart may be the wrong pairing:
=IF(E2="Unmatched","",ABS(A2-XLOOKUP(D2,Ledger!D:D,Ledger!A:A))>5)
TRUE means review the pair.
For unmatched bank lines, count ledger entries with the same amount within five days either side:
=COUNTIFS(Ledger!C:C,C2,Ledger!A:A,">="&A2-5,Ledger!A:A,"<="&A2+5)
A result of 1 is a likely match to confirm by eye. 0 means no candidate; more than 1 means you need the reference or description to decide.
When several receipts are banked together, the bank shows one line and the ledger shows several. Total the ledger receipts by batch reference:
=SUMIFS(Ledger!C:C,Ledger!E:E,"B0412")
If the total equals the bank deposit, mark all lines in that batch as matched. Card settlements work the same way, but the bank amount may be net of fees: the difference is the fee, which needs posting in the ledger.
The matched totals on both sides must agree:
=ROUND(SUMIFS(Bank!C:C,Bank!E:E,"<>Unmatched")-SUMIFS(Ledger!C:C,Ledger!F:F,"<>Unmatched"),2)
The answer should be zero. If it isn't, a pair has been matched on the wrong amount or a manual tick is wrong.
Then build the statement from bank balance to ledger balance. See bank reconciling items and the full bank reconciliation in Excel. A wider look at matching formulas is in matching transactions with XLOOKUP and COUNTIFS.
UK teams usually say "cash book" and "unpresented cheques"; US teams say "book balance" or "cash account" and "outstanding checks". The formulas are the same. XLOOKUP needs a current version of Excel; in older versions use INDEX and MATCH.
AI software that matches your bank statement to your ledger on your own computer and builds the reconciliation statement. $1,997 once-off; see it on the built-in example first.
The Bank Reconciliation Template reconciles up to five bank accounts across eight transaction categories, with an allocation check that confirms every transaction is coded before the reconciliation counts as clean. Excel, no macros, one-time price.
Add an occurrence counter to the amount, such as 250.00|1 and 250.00|2, on both sheets. Each key is unique, so each bank line pairs with only one ledger line.
Give the receipts a batch reference and total them with SUMIFS. If the total equals the bank deposit, mark the whole batch as matched.
Use COUNTIFS with a date window, for example five days either side, to find candidates, then confirm by reference or description.
Last updated 15 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.