Matching by amount alone works until two payments for the same amount arrive on the same day.
Put each side of the reconciliation on its own sheet, build a matching key from date and amount (plus a counter for repeats), then use XLOOKUP to find each item's match on the other side and COUNTIFS to flag duplicates. Anything returning 'Unmatched' is a reconciling item to explain.
Put the bank statement on a sheet called Bank and the cash book or ledger on a sheet called Ledger. Each needs a date, an amount (receipts positive, payments negative, the same convention on both sheets) and a reference.
On both sheets, with date in A and amount in B, add a key in D:
=TEXT(A2,"yyyy-mm-dd")&"|"&TEXT(ROUND(B2,2),"0.00")Rounding to two decimals avoids false mismatches from floating-point differences.
If the same amount appears twice on the same day, both rows share a key and XLOOKUP would match them both to the first one. Add an occurrence counter in E:
=D2&"|"&COUNTIF($D$2:D2,D2)The first occurrence gets |1, the second |2, so each matches its own counterpart.
On the Bank sheet, in F2:
=XLOOKUP(E2,Ledger!E:E,Ledger!C:C,"Unmatched")This returns the ledger reference for the matching item, or "Unmatched". Do the same on the Ledger sheet, looking up into Bank.
Items that cleared the bank a day or two after the ledger date will show as unmatched on both sides. For those, match on amount within a date window. On the Bank sheet, this counts ledger items with the same amount within three days:
=COUNTIFS(Ledger!B:B,ROUND(B2,2),Ledger!A:A,">="&A2-3,Ledger!A:A,"<="&A2+3)A count of 1 is a likely timing match. A count above 1 needs a person to decide.
Once items are matched, check the totals agree. The statement balance, plus deposits recorded in the ledger but not yet on the statement, minus payments recorded but not yet cleared, should equal the ledger balance. Bank charges, interest and direct debits on the statement but not yet in the ledger go the other way: post them to the ledger. If the adjusted figures still differ, a match was wrong or an item is missing.
Filter both sheets on "Unmatched". What remains is your reconciliation: outstanding payments, deposits in transit, bank charges not yet in the ledger and errors to correct. Each needs a reason. See how to do a bank reconciliation in Excel.
The method works for UK and US bank formats alike. Watch date formats when importing: a UK statement's 03/04 is 3 April, a US one is 4 March. Convert dates with DATEVALUE or Power Query before building keys.
Paste your invoice list and see likely duplicates in minutes. Free Excel file: enter your work email to download it.
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.
Microsoft 365, Excel 2021 and later, and Excel for the web. In older versions use INDEX and MATCH: =IFERROR(INDEX(Ledger!C:C,MATCH(E2,Ledger!E:E,0)),"Unmatched").
Batched receipts will not match one to one. Use the remittance advice to group the ledger items, sum them, and match the group total to the bank line.
They appear on the statement but not the ledger, so they show as unmatched. Post them to the ledger from the statement, then rerun the match. They should not stay as reconciling items into the next month.
Use reference first where both sides have a reliable one, such as a cheque number or payment ID, and fall back to date and amount for the rest.
Last updated 18 October 2026. General guidance for finance teams; check it against your own policies, systems and local rules.