Bank reconciliation in Excel: matching bundled deposits and partial payments when XLOOKUP gives up

A poster on r/tax described the wall everyone hits: "clients make partial payments that don't match the invoice totals" and "Excel's VLOOKUP/XLOOKUP isn't cutting it because the unique identifiers don't align perfectly." The formula is not the problem. XLOOKUP assumes every row on one side matches exactly one row on the other, joined by a shared key — and bank data breaks that assumption in both directions. One deposit bundles fifty transactions into a single line. One invoice gets paid in three installments. The fix is not a cleverer lookup. It is switching what you compare: totals against totals for bundled deposits, running balances for partial payments, and — for the last few orphans — a combination-sum trick you should use sparingly. This guide covers all three, in the order that keeps you sane.

Why does XLOOKUP give up on bank reconciliation?

XLOOKUP searches a range and returns the item corresponding to the first match it finds. That is the whole design: one lookup value, one matching row, one answer. It reconciles two well-behaved exports beautifully. A bank statement is not well-behaved. A poster on r/Accounting trying to reconcile merchant-processor activity against a GL put it plainly: "There is also no common identifiers besides the amount and SOMETIMES the date." No shared key, and no one-to-one rows to join even if there were one. Three shapes show up in real bank data, and only the first is XLOOKUP's job:

Threads asking about this get strikingly little help. The r/tax poster's only substantive reply was a diagnostic question — "Are you reconciling settlement report vs actual bank credit? Sometimes fee deductions + refunds create mismatch." — a fair point, but not a method. And when an r/Accounting poster asked how much month-end reconciliation still happens manually in Excel, naming "matching transactions between exports" and "dealing with missing or slightly different references," the replies accused the poster of vendor self-promotion instead of answering. The method below is the answer those threads never got.

Step 0: clean the keys, or nothing below works

Every technique on this page compares amounts, dates, and reference strings. If those columns are quietly broken, you will manufacture false mismatches and then spend an evening chasing them. Before anything else: amounts stored as numbers, not text; dates as real dates, not strings that sort wrong; and one clean-key helper column on each sheet — trimmed, prefix-stripped, case-consistent — the same preparation covered in the XLOOKUP guide and, for exports that arrive already mangled, the CSV gotchas that break reconciliations. If your references are near-misses rather than exact matches — ORD-1042 against 1042 — normalize them before you match rather than teaching every formula to forgive them.

How do you match one bundled deposit to many transactions?

Stop trying to match the deposit to any single row — it is not any single row. A bundled deposit is a batch total, so build the batch total on your side and compare totals. A commenter on r/Bookkeeping nailed the most common version of the mismatch: "the mismatch is usually because QBO records the gross sale amount but your bank gets the net deposit after processing fees. if you're recording both and trying to match them 1:1 they'll never line up." So the batch total has to be net — gross minus fees and refunds — before it will ever equal the deposit. The mechanics:

1. On the ledger sheet, add a Batch column that groups rows the way the bank bundles them: the settlement batch ID if your processor provides one, otherwise the expected payout date.
2. Add a Net column per row: gross amount minus that row's share of fees and refunds.
3. On the bank sheet, next to each deposit, total the matching batch with SUMIFS.
4. Compare the batch total to the deposit amount, rounded to cents, and flag the difference.
5. Work only the non-zero rows: drill into that one batch, not the whole month.

=SUMIFS(Ledger!$D:$D, Ledger!$B:$B, A2)

where Ledger!D = net amount, Ledger!B = batch/payout date, A2 = deposit date

=IF(ROUND(C2-B2,2)=0, "ok", "diff " & TEXT(C2-B2, "0.00"))

This is the same per-payout logic that makes Shopify deposits reconcilable and marketplace payouts tie out through a clearing account — the deposit is a settlement batch, so the batch is the unit of reconciliation. It also matters for cleanup later: as a commenter on r/Bookkeeping noted about matching a deposit to an invoice inside a bigger deposit, "it brings the items in individually which can be a pain for verifying the reconciliation" — batches you split apart by hand are batches you re-verify by hand.

How do you reconcile partial payments against one invoice?

Flip the comparison. Instead of asking "which payment matches this invoice?" — there is no single one — ask "how much has been paid against this invoice so far, and does the remainder make sense?" That is a running balance, and SUMIFS builds it in one column:

1. Put invoices on one sheet with the invoice key and total; payments on another with the invoice key and amount. Normalize both keys in a helper column first.
2. On the invoice sheet, compute paid-to-date per invoice with SUMIFS against the payments sheet.
3. Compute the open balance: invoice total minus paid-to-date.
4. Add a status column: OPEN when nothing has been paid, PARTIAL when something has, PAID at zero remaining, OVERPAID when payments exceed the total.
5. Review PARTIAL rows by age and OVERPAID rows immediately — overpayment is how a duplicate payment announces itself.

=SUMIFS(Payments!$C:$C, Payments!$A:$A, A2)

=IF(D2=0, "OPEN", IF(ROUND(B2-D2,2)=0, "PAID", IF(D2<B2, "PARTIAL", "OVERPAID")))

where B2 = invoice total, D2 = paid-to-date

The honest catch: this assumes each payment carries the invoice key. The r/tax poster's payments did not — that is what "the unique identifiers don't align perfectly" means in practice. When the key is missing or mangled, allocate by customer instead: filter payments to the customer, apply them to that customer's open invoices oldest-first, and flag anything ambiguous for a human decision. Do not let a formula silently guess which invoice a keyless payment belongs to. A primary ID is exactly what is missing here, and pretending you have one is worse than knowing you do not.

What about a deposit with no key at all — the combination-sum trick?

After batch totals and running balances, you will usually have a small residual: a deposit or two that matches nothing, with no reference to work from. The classic advice — from that same r/Accounting credit-card thread, where a commenter suggested you "find combination of transactions that add up to an amount" — is the subset-sum problem, and Excel's Solver add-in can brute-force it:

1. List the candidate transactions in one column and add a 0/1 flag column beside them.
2. Compute the selected total with SUMPRODUCT of the amounts and the flags.
3. In Solver: set the objective to that total equalling the deposit amount, the variable cells to the flag column, and a constraint that the flags are binary.
4. Solve. The rows flagged 1 are a combination that sums to the deposit.

=SUMPRODUCT($B$2:$B$50, $C$2:$C$50)

Solver: objective = deposit amount · variables = C2:C50 · constraint = C2:C50 bin

Use this on a handful of stubborn rows at the end, not as the method. If you are running combination-sums every week, the real problem is upstream — the export is missing the reference that would make matching deterministic. An hour of fixing that at the source beats a lifetime of solving puzzles downstream.

Where the spreadsheet method runs out

These three techniques will get a monthly bank rec done in Excel, and done defensibly — the full statement-to-books procedure lives in bank reconciliation, step by step, and a ready-made layout in the reconciliation spreadsheet template. But notice what the workbook does not keep: the reasoning. Which batch rule you used, why you allocated a keyless payment to that invoice, which Solver result you accepted. That is precisely what an auditor asks for, and re-deriving it from cell formulas months later is its own reconciliation. When the same comparison runs every week, when partial payments and bundled deposits are the norm rather than the residual, or when someone else has to trust the number, the logic needs to live somewhere repeatable — whether that is a carefully prompted AI assistant, a script, or a dedicated tool. Until then: totals to totals, balances to balances, and Solver only when cornered.

← All writing