Reconciliation at Scale: Matching Algorithms Beyond Simple Reference Lookups

A post-mortem, of sorts. What we learned building a matching engine that had to handle thousands of transactions daily — and why the first three versions didn't work.

Version 1: The VLOOKUP Era

The first reconciliation process we ever built for a payment platform wasn't really a process. It was a spreadsheet. Bank statement goes into column A-F. Internal transactions go into a second tab. VLOOKUP on the reference field. Conditional formatting: green means matched, red means investigate.

It worked. For about four months.

The operator doing the reconciliation — one person, twice a day — could handle 80 transactions in a sitting. Then the client onboarded three new merchants and volume jumped to 300 transactions daily. The VLOOKUP still ran, but the operator couldn't investigate the 40+ red cells every day. They started triaging: anything under €10,000 got marked as "probably fine." Month-end found €180,000 in discrepancies that had been "probably fine" for six weeks.

That's when we built the first proper matching engine. And then rebuilt it. And then rebuilt it again.

Version 2: Exact Reference Match (and its limits)

The obvious first step: take the reference number from the bank statement, find it in the internal ledger, compare the amounts. Automated VLOOKUP, essentially.

This handled about 75% of transactions cleanly. The other 25% fell into patterns we hadn't anticipated:

Banks truncate references. We'd send a payment with reference "INV-2024-03-15-MERCHANT-ACME-CORP." One of the banks would show "INV-2024-03-15-MER." Sixteen characters, hard cut. Another bank would strip hyphens first, then truncate. A third would truncate but pad with spaces. Same payment, three banks, three different strings.

References collide. Two merchants with simple invoicing systems both sending "INV001" in the same week. The matching engine found two candidates, picked the first one alphabetically (because that's what the ORDER BY did), and silently matched the wrong transaction. We didn't catch it until the merchant called asking about a payment they never received.

Banks add their own prefixes. We'd send "PAY-12345" and the statement would show "BANKX-PAY-12345" or "BANKY/PAY-12345." Our exact-match logic saw these as completely different strings.

Version 2 lasted two months before the exception queue was longer than the matched queue.

Version 3: The Matching Hierarchy

This is where we stopped being clever and started being systematic.

Instead of trying to write one perfect matching algorithm, we built a hierarchy — a cascade of increasingly fuzzy matching strategies, each with a confidence score. Transactions flow through the hierarchy top to bottom. The first match that meets the confidence threshold wins. Unmatched transactions continue to the next level.

Here's the hierarchy we settled on, after more iteration than we'd like to admit:

Level 1: Exact match. Same reference (after normalisation), same amount, same date. Confidence: 100%. Auto-reconcile, no human needed.

Normalisation matters. Before comparing, we strip whitespace, remove hyphens and slashes, convert to uppercase, and remove known bank prefixes. "BANKX-PAY-12345" and "pay 12345" both normalise to "PAY12345." This alone moved our exact-match rate from 75% to 85%.

Level 2: Reference match, amount within tolerance. Same normalised reference, but the amount differs by a small, predictable amount. Usually a bank fee — €12.50 here, €25 there. We maintain a fee table per bank, per transaction type. If the difference matches a known fee, confidence: 95%. Auto-reconcile with a fee flag.

Level 3: Partial reference match. The normalised reference is a substring of the bank's reference (or vice versa), and the amount matches. This catches truncation. Confidence: 90%. Auto-reconcile if the match is unambiguous (only one candidate). If multiple candidates, drop to Level 6.

Level 4: Amount + date match. No reference match at all, but a unique amount within a ±2 business day window on the same account, same direction (debit/credit). Confidence: 70%. Flag for review — don't auto-reconcile, because amount-only matching is inherently ambiguous.

Level 5: Batch match. Multiple bank statement entries sum to a single internal transaction (the bank split a large payment) or a single bank entry matches the sum of multiple internal transactions (the bank batched them). We look for combinations within a date window whose sum matches within tolerance. Confidence: 75%. Flag for review.

Level 6: Ambiguous match. Multiple candidates at any level. The engine presents all candidates with their confidence scores. An operator picks. Confidence: varies. Always requires human decision.

Level 7: No match. Nothing in the hierarchy matched. Exception queue. Could be a timing issue (payment hasn't settled yet), a missing transaction, or a real problem.

Each level runs in order. Once a transaction is matched at one level, it's removed from the pool for subsequent levels. This prevents double-matching — an insidious problem where the same bank statement entry gets claimed by two different internal transactions.

What we got wrong (and fixed)

The first batch-matching implementation was O(n³). We were checking every combination of bank entries against every internal transaction. At 100 unmatched entries, it ran in seconds. At 500, it took four minutes. At 1,000, we killed it after twenty minutes. The fix was pre-grouping by date and amount range before attempting combinations. Not glamorous, but it brought the runtime under 10 seconds for any realistic volume.

We auto-reconciled ambiguous amount matches. For about a week. Two €45,000 payments on the same day, different merchants. The engine picked one. It picked wrong. The merchant who should have been matched called three days later. Now: if the amount isn't unique within the date window, it goes to a human. Always.

We didn't track match rates. For the first two months we didn't monitor what percentage of transactions matched at each level. When we finally added the metrics, we discovered that our Level 1 (exact match) rate had dropped from 85% to 68% over six weeks. One of the banks had changed their reference format in a way that broke our normalisation. Nobody noticed because the overall match rate was still "fine" — the slack was being absorbed by lower-confidence levels and manual work. Now we alert if any level's match rate drops more than 5% week-over-week.

We forgot about the time dimension. A transaction sent on Friday afternoon might not appear on the bank statement until Monday. Our initial ±1 day window missed these. We moved to ±2 business days (not calendar days — we maintain a holiday calendar per banking jurisdiction). The match rate for cross-weekend transactions went from 60% to 95%.

The exception queue is the actual product

Here's the thing we didn't understand until we'd been running in production for six months: the matching algorithm isn't the product. The exception queue is.

The algorithm's job is to handle the 85-90% of transactions that match cleanly. The exception queue is where the remaining 10-15% gets resolved — and that's where discrepancies are caught, fees are identified, fraud is detected, and operational issues surface.

A good exception queue shows:

• Why the match failed (which level it reached before giving up)
• The engine's best guess, with confidence score
• Both sides of the data: internal record and bank statement entry, side by side
• Resolution actions: approve suggested match, link to a different transaction, create a fee adjustment, flag for investigation

The goal isn't zero exceptions. The goal is that every item in the queue is genuinely worth a human's time, and resolving it takes thirty seconds, not thirty minutes.

We measure two things: exception queue size (should be stable or declining day over day) and average resolution time (should be under a minute for routine items). If either metric trends the wrong way, something has changed — a new bank, a new merchant, a format update — and the matching rules need attention.

Zenlime builds reconciliation engines that handle the gap between what your system believes and what the bank confirms. Let's talk.