Invoice to payment matching template
By the Analistable team · Updated · 2 min read
A free Excel and Google Sheets workbook: paste your invoice list and your payment receipts with the invoice reference each payment quotes. SUMIFS adds up what was paid against each invoice and labels it Paid, Part paid, Overpaid or Unpaid, while payments with no matching invoice are flagged separately.
Part of our guide: How to reconcile data in Excel
What the template does
Payments rarely line up one-to-one with invoices: customers pay in instalments, short-pay, pay twice or forget the reference. This template sums every payment quoting an invoice number, so part payments add up on their own, and it lists the cash that matches nothing. It is the spreadsheet version of matching Stripe payments to invoices and sits under the reconciling data in Excel guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Invoices | Invoice no, Customer, Invoice date, Due date, Amount | Yes |
| Payments | Payment date, Payer, Invoice ref, Amount, Match | First four columns |
| Matching | Invoice no, Customer, Amount, Paid, Outstanding, Status | No — calculated |
How to fill it in
- Export open and recently paid invoices from your accounting tool into Invoices.
- Paste bank or payment processor receipts into Payments. Put the invoice number each payment refers to in Invoice ref; bank references often need tidying first (e.g. 'INV1003 THANKS' → 'INV-1003').
- Where one payment covers two invoices, split it into two rows.
- Anything marked 'Unmatched payment' needs a reference found or a credit note raised.
How the formulas work
Paid adds every payment quoting the invoice number:
=SUMIFS(Payments!$D$2:$D$500,Payments!$C$2:$C$500,A2)Status reads the paid and outstanding columns:
=IF(D2=0,"Unpaid",IF(E2=0,"Paid",IF(E2>0,"Part paid","Overpaid")))On Payments, the Match column checks the reference exists:
=IF(COUNTIFS(Invoices!$A$2:$A$500,C2)>0,"Matched","Unmatched payment")Worked example
| Invoice | Customer | Amount | Paid | Outstanding | Status |
|---|---|---|---|---|---|
| INV-1001 | Acme | 1200 | 1200 | 0 | Paid |
| INV-1002 | Birch & Co | 850 | 850 | 0 | Paid |
| INV-1003 | Coastal | 2400 | 2400 | 0 | Paid |
| INV-1004 | Acme | 600 | 0 | 600 | Unpaid |
| INV-1005 | Delta | 1750 | 1700 | 50 | Part paid |
| INV-1006 | Elm Studio | 320 | 320 | 0 | Paid |
| INV-1007 | Birch & Co | 990 | 1090 | -100 | Overpaid |
| INV-1008 | Fern | 1500 | 0 | 1500 | Unpaid |
| Total | 9610 | 7560 | 2050 |
INV-1003 was paid in two instalments and still comes out as Paid. One receipt of 450 has no usable reference, so it shows as unmatched on the Payments sheet and in the total below the Matching table. To chase old balances, see finding unpaid invoices older than 30 days.
Limits and when to move on
The template depends on a clean invoice reference on each payment. When references are free text, amounts are netted of fees, or you have thousands of rows a month, matching becomes a fuzzy job: Power Query's fuzzy merge helps, or load both exports into Analistable and ask 'which payments don't match an invoice by amount and customer?'. Nothing is uploaded to a server.
Frequently asked questions
- Can it match on amount when there is no reference?
- Not automatically. Use COUNTIFS on customer and amount as a helper to suggest likely matches, then type the invoice number into Invoice ref.
- How do I handle card processor fees?
- Record the gross amount the customer paid, not the net payout, or add the fee as a separate row with the same invoice reference.
- Does it age the debt?
- No, to keep it free of volatile TODAY() formulas. Add a column with =TODAY()-D2 on Invoices if you want days since due.