Payment Reconciliation

I built this workbook to compare a fictional payment ledger with a partner report. I checked for missing transactions in both directions and calculated the net difference for transactions that appeared in both files.

Workbook previews

Ledger excerpt showing matched amounts, amount differences, and a missing partner transaction — Ledger results by transaction ID.

Partner report excerpt showing TXN-999 as missing from the ledger — The reverse lookup flags a transaction found only in the partner report.

Project questions and answers

How did I match transactions between the reports?: I used XLOOKUP to match by transaction ID, so the reports could be in different orders. I used IFS to classify each ledger row and COUNTIF to count the results.
What differences did the reconciliation find?: Of the 36 ledger transactions, 30 matched, 3 had different amounts, and 3 were missing from the partner report. For transactions present in both reports, the partner amounts totalled $75.00 CAD more than the ledger amounts.
Did the partner report contain a transaction missing from the ledger?: Yes. I reversed the lookup to check each partner transaction against the ledger. TXN-999 was the only transaction found exclusively in the partner report.

Download workbook (.xlsx)View report (.pdf)