Spreadsheets

Three-way match of invoice, bank and ledger in one sheet

Build a three-way match of invoice, bank and ledger in one sheet with lookups on invoice number and amount, giving statuses like Matched and Paid not booked.

Editorial illustration for Three-way match of invoice, bank and ledger in one sheet

A three-way match checks each invoice against two other records: what the bank received and what your ledger booked. In one sheet, three lookups on the invoice number fill Invoice, Bank and Ledger columns, and a single status formula turns the pattern into words such as Matched, Paid not booked or Booked not paid.

What three tabs do you need?

  • Invoices: invoice number in A, customer in B, amount in C. This is the master list and the sheet where the match lives.
  • Bank: date in A, narration in B, invoice number in C, credit amount in D.
  • Ledger: invoice number in A, date in B, amount booked in C.

The invoice number on the Bank tab is the bridge. You can type it after reading each narration, or extract it with a formula when narrations contain it. In Excel 365, this works in Bank!C2: =IFERROR(INDEX(Invoices!$A$2:$A$100, MATCH(TRUE, ISNUMBER(SEARCH(Invoices!$A$2:$A$100, B2)), 0)), ""). In older Excel, confirm the formula with Ctrl+Shift+Enter. It looks for any invoice number inside the narration.

Which lookups fill the columns?

On the Invoices tab, add two columns beside the amount. Use SUMIFS, not VLOOKUP, because a customer may pay an invoice in two parts and you want the total:

D2 (received in bank): =SUMIFS(Bank!$D$2:$D$500, Bank!$C$2:$C$500, A2)

E2 (booked in ledger): =SUMIFS(Ledger!$C$2:$C$500, Ledger!$A$2:$A$500, A2)

What is the status formula?

In F2, using C for invoice amount, D for bank and E for ledger:

=IF(AND(D2=C2, E2=C2), "Matched", IF(AND(D2>0, E2=0), "Paid not booked", IF(AND(D2=0, E2>0), "Booked not paid", IF(AND(D2=0, E2=0), "Open", "Amount differs"))))

The order matters: the formula checks the perfect case first, then the two lopsided cases, then the case where nothing has happened, and finally lands on Amount differs for anything else, such as a part-payment.

What does it produce on sample invoices?

InvoiceAmountBankLedgerStatus
INV-20112,00012,00012,000Matched
INV-20225,00025,0000Paid not booked
INV-2038,40008,400Booked not paid
INV-20415,00000Open
INV-2056,0005,9006,000Amount differs

Each status points to a different action. Paid not booked means you must record the receipt. Booked not paid means the ledger shows money that the bank never saw, which needs urgent attention. Open is normal until the due date. Amount differs usually means a deduction, a bank charge or a short payment, and deserves a note.

A worked reading of the sample: INV-201 is clean and needs nothing. INV-202 is a receipt of ₹25,000 sitting in the bank with nothing in the ledger, so the books understate what you have collected. INV-203 is the reverse and the more worrying one, since your ledger claims ₹8,400 that the bank has not shown. INV-205 differs by ₹100, which could be a bank charge or a short payment, and needs a note before you adjust anything.

How do you summarise the results?

Add a small table with the five statuses and a count for each: =COUNTIF($F$2:$F$100, "Paid not booked"). Beside it, total the amounts per status with SUMIFS. Review the two lopsided categories before anything else, because those are the ones where your books and your bank disagree.

Part-payments deserve a thought. If a customer pays INV-205 in two instalments of ₹3,000 each, SUMIFS adds them to ₹6,000 and the row turns Matched once both land. That is the reason to use SUMIFS rather than a single-row lookup. The status will say Amount differs in the meantime, which is accurate.

Keep it honest

  1. Check that every invoice number is unique, otherwise SUMIFS adds the wrong rows.
  2. Re-run the sheet after each bank import and each ledger posting.
  3. Treat Booked not paid as a priority, not a footnote.
  4. Keep a note column for what you did about each non-matching row.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports