Bank Reconciliation in Excel: A Lookup-Based Technique

What this technique actually answers

Bank reconciliation answers one question: does what the bank says happened match what your books say happened? Most of a bank statement will match cleanly — it’s the handful of lines that don’t that need a human to look at them. The goal of this technique isn’t to automate reconciliation end to end; it’s to get a computer to do the matching so a person only has to review what’s actually unclear.

The formula

For each bank credit, check whether that exact amount also appears as an outstanding amount on your receivables list. If it does, XLOOKUP returns the matching invoice number — proof of a match. If it doesn’t, XLOOKUP returns #N/A, and IFERROR catches that and labels the row “Unmatched” instead of letting the error propagate:

=IFERROR(XLOOKUP(CreditAmount, Receivables!OutstandingAmount, Receivables!InvoiceNo), "Unmatched")

Once every bank line carries a Matched/Unmatched flag, filter the statement down to just the Unmatched rows. That filtered list — not the full statement — is the only thing that needs a human to look at it.

A worked example

Applied to a real monthly statement of 90 bank transactions, this technique flagged 6 as Unmatched. On review, those 6 broke down into:

• —2 were bank interest credited directly by the bank — expected, no entry missing, no action needed.
• —3 were timing differences — payment received by the bank but not yet recorded in the books (or vice versa).
• —1 was a genuine discrepancy requiring follow-up with the customer.

That’s the real value of the technique: it doesn’t eliminate judgment, it concentrates it — 6 lines to actually think about instead of 90.

Mistakes that cause false matches

• —Treating every “Unmatched” as an error. It isn’t — investigate first. Interest, bank charges and timing differences are all normal and expected.
• —Matching purely on amount, with no date check. Two unrelated transactions can share an identical amount by coincidence. Sanity-check date proximity before accepting a match.
• —Calling this “automated reconciliation.” It isn’t — it’s automated matching. The exception list still needs a human to review it and reach a conclusion.

What this technique doesn’t cover

This is a transaction-matching technique using a clean bank statement export and a list of expected receipts — it does not parse or import raw bank-statement files, which vary significantly by bank and format. If your real starting point is a PDF or CSV export straight from online banking, that file typically needs cleaning into a consistent table first (which Power Query, not this technique, is usually the right tool for) before the matching formula above applies.