Auto Bank Reconciliation in Excel With Matching Formulas

Auto bank reconciliation in Excel works by giving each cash book entry and each bank line a match key, then using XLOOKUP or COUNTIFS to find its partner on the other sheet. Matched rows need no attention. A FILTER formula lists the rest, and those unmatched rows become the reconciling items for the month.

Match keys for automatic Excel reconciliation

A match key is a value built from one or more fields that should be identical for the same transaction in the cash book and on the bank statement. Amount is the base of every key. Check number makes a strong key for checks. Date is a poor key alone, because the bank posts after the books do.

Each field has strengths and weaknesses:

• Amount. Present on both sides and rarely mistyped by the bank. It fails when two transactions share an amount, such as two $1,500.00 customer deposits in one month.
• Check number. Unique for checks and usually printed on the statement. It is useless for deposits, transfers and card payments.
• Date. The books record a check when it is written and the bank records it when it clears. A match on exact date misses most checks.
• Description. Bank descriptions are abbreviated and change format between banks. Avoid it as a key.

A sound key, then, is the check number when there is one and the amount otherwise, with a counter added to tell repeated amounts apart. The section on same-amount duplicates builds that counter.

Both sheets need the same sign convention before any key works. Record deposits as positive and payments as negative on both the cash book and the bank sheet. If a bank export puts withdrawals and deposits in separate columns, combine them into one signed amount column first.

The key lives in a helper column. Excel's ampersand operator joins values into one piece of text; Microsoft's page on calculation operators describes it as connecting two values to produce one continuous text value. A key such as 1500.00|2 is the amount and an occurrence number joined by a bar. The amount goes through the TEXT function first, which Microsoft's TEXT function reference describes as converting a number to text in the format you give it, so both sheets write the amount the same way with two decimal places.

XLOOKUP and COUNTIFS formulas that match bank lines

Two formulas do the matching. COUNTIFS counts how many bank lines share a cash book row's key, which says whether a match exists. XLOOKUP returns a field from the matching bank line, such as its date, which shows where the match is and how long it took to clear. Both copy down the whole sheet.

Microsoft's XLOOKUP reference gives the syntax as XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Exact match is the default, and the optional if_not_found argument sets the text returned when nothing matches; without it, the result is #N/A. The same page notes that XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024, and not in Excel 2016 or 2019.

Microsoft's COUNTIFS reference gives the syntax as COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…). It accepts up to 127 range and criteria pairs, and each additional range must have the same number of rows and columns as the first.

The formulas for the example workbook, entered in row 2 of each sheet and copied down:

The key formula reads: if the row has a check number in column B, the key is "CHK" and that number; otherwise it is the amount with two decimals, a bar, and how many times that amount has appeared so far on the sheet. The range $D$2:D2 grows as the formula is copied down, because the first reference is absolute and the second is relative, as Microsoft's page on relative, absolute and mixed references explains.

Microsoft's FILTER reference gives the syntax as FILTER(array, include, [if_empty]). Without the third argument, a filter with no results returns a #CALC! error, so the formulas above return "None" instead. FILTER spills its results into the cells below, so leave the area under each formula empty.

How do you match a cash book against the bank statement in Excel?

To match a cash book against the bank statement in Excel, put each on its own sheet with identical columns, build the same helper key on both, and point each sheet's match formula at the other's key column. The cash book looks up the bank and the bank looks up the cash book, so unmatched rows show on both sides.

The example workbook uses fictional September 2026 figures for one client checking account, XXXX-4821. Both sheets use columns A to D for date, check number, description and signed amount, and columns E and F for the key and the match.

Cash book sheet:

Bank sheet:

The exceptions sheet then shows two cash book rows (check 1187 and the 3,400.00 deposit) and two bank lines (the fee and the interest). Those four rows are the whole of the month's work. Every other row paired with a partner on the other sheet without anyone ticking it off.

Rows should be kept in date order on both sheets. The occurrence counter numbers repeated amounts from the top down, so the first $1,500.00 deposit in the books pairs with the first $1,500.00 deposit on the bank sheet.

Same-amount duplicates that fool Excel matching

Same-amount duplicates are two or more transactions with the same value in one period, such as recurring deposits or identical subscription charges. A key built from amount alone gives both rows the same key, so XLOOKUP returns the first bank line for both and a missing second line goes unnoticed. The occurrence counter fixes that.

Without the counter, the cash book's two $1,500.00 deposits would both find the bank's first $1,500.00 line. If the client's second deposit never reached the bank, both cash book rows would still show a match, and the missing deposit would hide inside the reconciliation. With the counter, the second cash book deposit carries key 1500.00|2, and if the bank has only one such deposit, that row shows "No match".

The counter has a limit worth knowing. It pairs repeated amounts by order, not by evidence. If the books record two equal deposits on 9/5 and 9/18 but the bank posts them on 9/19 and 9/22, they still pair first-to-first. The totals are correct, but the dates in the match column may describe the wrong pairing. Glance at the gap between the book date and the matched bank date; a large gap is worth a look.

Three other duplicate traps:

• A check and another payment with the same amount. The counter counts every row with that amount, including check rows. If the books hold an outstanding $50.00 check and a $50.00 card payment, the card payment's counter can differ from the bank side's. When a non-check row fails to match, look for a check of the same amount above it.

• A genuine double entry in the books. If a deposit was entered twice, the counter gives the copy key 1500.00|2, it finds no bank partner, and it lands on the exceptions list. That is the correct outcome: the exception is an error to correct, not a deposit in transit.

• Combined deposits. Two customer checks deposited together appear as one bank line for their total. No key matches a sum to its parts. These rows always land on the exceptions list and need a person to pair them.

Turning matched rows into a monthly bank reconciliation statement

A monthly bank reconciliation statement in Excel takes the unmatched rows and sorts them into the standard reconciling items: unmatched cash book deposits are deposits in transit, unmatched cash book payments are outstanding checks, and unmatched bank lines are items the books have not recorded yet. SUMIFS totals each group straight from the match columns.

Microsoft's SUMIFS reference gives the syntax as SUMIFS(sum_range, criteria_range1, criteria1, …) and points out that the sum range comes first in SUMIFS but third in SUMIF. Comparison operators in criteria go inside quotation marks, as in ">0".

These four totals feed the bank side and book side of a reconciliation statement. The layout of that statement, block by block, is set out in the post on the bank reconciliation format in Excel, so it is not repeated here. Link the four cells above into its deposits in transit, outstanding checks, unrecorded credits and unrecorded debits rows.

Before treating every unmatched row as a timing item, read the exceptions list. An unmatched book row could be a duplicate, a transposed amount or a check entered under the wrong number. Only items that will clear later belong in deposits in transit or outstanding checks.

When should Excel matching give way to software?

Excel matching gives way to software when the time spent maintaining the workbook outgrows the time it saves. The signs are practical: many accounts per client, many clients each month, frequent same-amount and combined deposits, bank exports that change format, and statements that arrive as PDFs needing to be keyed before any formula can run.

No volume threshold fits every firm, and a workbook that suits one bookkeeper with three clients may strain a team with forty. Ask a few questions. How often does the exceptions list hold false mismatches that someone has to clear by hand? How often does a bank change its export columns? Does anyone besides the author understand the key formula? When the answers start to worry you, accounting software's own reconciliation screens are the next step. The walkthroughs for reconciling in QuickBooks Online and reconciling a bank statement in QuickBooks Desktop cover those screens.

Getting statement data into the bank sheet is often the slowest step. Schema CPA reads the bank statements a client uploads, checks that each month's balances carry on from the last, and exports the results to Excel or CSV for the firm to work with. The document types it reads are listed on the homepage, and the bank statements to Excel category covers conversion in more depth.