Two lists that should agree, and the reasons they do not RECONCILE

The interesting rows are the ones that nearly match

Reconciliation is the same job whatever it is called: a bank statement against a ledger, a stock count against a system, an invoice list against a remittance, a payroll file against a rota. Two lists that ought to describe the same events, and the work is finding the ones that do not pair up.

Done by eye it takes hours and misses things. Done with a lookup formula it finds the exact matches and silently drops everything interesting on the floor, because the interesting cases are precisely the near misses. The right reference with the wrong amount. The right amount with a reference that has a space in it. A reference typed with an O instead of a zero. One payment covering three invoices. A refund. A duplicate.

A lookup returns #N/A for all of those, which looks identical to “not there”, and the ordinary response to a column of #N/A is to work down it by hand, which is where the hours went.

Matching in passes, hardest evidence first

So this matches in stages and reports what each stage consumed. Exact key first. Then the key with punctuation, spacing and case taken out. Then amount within a tolerance. Then the looser tests.

The order matters, and so does the reporting. Running the loose tests first would pair things by coincidence: two invoices for the same round number in the same month will match on amount and be wrong. Taking the strongest evidence first means every row is claimed by the best reason available, and a row that survives to a later pass is a row you should look at.

The counts are the check on your own assumptions. “Exact key: 412. Normalised key: 38. Amount within tolerance: 6. Unmatched: 11” tells you something no total can: that 38 references in your data are formatted inconsistently, which is a problem worth fixing upstream rather than reconciling around every month.

The unmatched list is the output

Everything that pairs is, by definition, not news. What you came for is the residue, and each kind of residue means something different:

• In the bank, not in the ledger: a fee, a payment you were not told about, a customer who paid without reference, or money that is not yours.
• In the ledger, not in the bank: timing, usually, and if it is still unmatched next month it is not timing.
• Matched on amount but not on reference: almost always a data-entry difference, and occasionally two genuinely different transactions that happen to be the same size.
• One against many: a consolidated payment, which is correct and which no pairwise match will ever resolve on its own.

Do it monthly. A discrepancy found within weeks is still a question somebody can answer; the same one found in January is a line nobody remembers and usually gets written off. Nothing you load is uploaded. Nearby: LEDGER is the cash book on one side of it, SPLIT cuts a CSV into the shape you need first, CLEANSHEET tidies the values, and MATCH handles the harder problem of matching names rather than references.