Supplier statement reconciliation template: columns and formulas

If you reconcile supplier statements (vendor statements, in most US AP teams) in a spreadsheet, the layout decides how long it takes. A sheet built as one long list you tick by eye takes an afternoon per big vendor. A sheet that looks every statement line up against your AP export does most of the comparing for you, and leaves you with a short list of lines to explain.

This guide lays out that second kind of template: three tabs, the columns on each, the lookup formulas, and a status column that sorts every line into matched, paid, missing, credit or mismatch. It works in Excel 365 and Google Sheets. If you are new to the process itself, start with vendor statement reconciliation and come back.

The three tabs

1. Statement: the vendor's lines, pasted or typed, one row per line.
2. AP: your open items for that vendor, exported from your ERP as of the statement date.
3. Payments: your payments to that vendor for roughly the last 90 days.

Keep one workbook per vendor per month, named like Midwest Fastener 2026-09.xlsx, or one workbook per month with the vendor ID as a column on every tab. The per-vendor workbook is easier to start with; the monthly workbook is easier once you reconcile more than ten or so vendors.

The Statement tab

At the bottom of column E, add the statement total and compare it with the closing balance printed on the statement. If they differ, a line was missed when the statement was copied in, and every status below is suspect until you find it.

The doc key: comparing numbers that are written differently

The vendor prints INV-0048811; your ERP stores 48811. A plain lookup will call that a missing invoice. The doc key strips the noise so both sides compare equal. In Google Sheets:

=IF(REGEXMATCH(A2&"","[0-9]"), TEXT(VALUE(REGEXREPLACE(A2&"","[^0-9]","")),"0"), UPPER(REGEXREPLACE(A2&"","[^A-Za-z0-9]","")))

When the number has digits, that keeps only the digits and drops leading zeros; when it has none, it keeps the letters and digits in capitals. Recent Excel 365 builds have REGEXREPLACE as well, with REGEXTEST in place of REGEXMATCH. Older Excel needs a nested SUBSTITUTE for each prefix you see (INV, CM, #, -, spaces). Document numbers longer than 15 digits lose precision in VALUE; keep those as text.

Be careful with vendors whose numbers differ only by a letter, such as 48811 and 48811A for an invoice and its rebill. If you see those, use the letters-and-digits version for that vendor.

Add the same doc key column to the AP and Payments tabs.

The lookups

On the Statement tab, with the AP tab's doc key in column B and amount in column E:

F2: =IFERROR(XLOOKUP(B2, AP!B:B, AP!E:E), "")
G2: =IFERROR(XLOOKUP(B2, Payments!C:C, Payments!E:E), "")

In the Payments tab, put the invoice the payment covered in column C (as a doc key) if your system exports it. If it only exports check numbers, look payments up by check number for payment lines on the statement, and match invoices to payments by hand.

The status column

H2: =IF(D2="Credit", IF(F2="", "Credit not applied", IF(ABS(E2-F2)<0.01, "Matched", "Mismatch")),
     IF(D2="Payment", "Payment",
     IF(F2<>"", IF(ABS(E2-F2)<0.01, "Matched", "Mismatch"),
     IF(G2<>"", "Paid", "Missing in AP"))))

Conditional formatting makes the sheet readable at a glance: red for Missing in AP and Credit not applied, amber for Mismatch, no fill for Matched and Paid. Then filter column H and you have the month's exception list for that vendor.

The other direction: what the statement leaves out

Two checks the Statement tab cannot do by itself:

• Your payments the vendor has not posted. On the Payments tab, look each payment up against the statement's payment lines or against the invoices it paid. A payment older than a week or two that the statement still shows as unpaid is one to send remittance details for.
• Open items the statement does not list. On the AP tab, look each doc key up against the Statement tab. Most of these are timing. A large or old one is worth a question; it may be an invoice the vendor already counts as paid.

A checklist for each vendor

1. Paste the statement lines; check the total against the printed closing balance.
2. Paste the AP open items and payments for that vendor, as of the statement date.
3. Fill the formulas down; filter Status for anything other than Matched and Paid.
4. For each Missing in AP line, ask the vendor for a copy of the invoice.
5. For each Credit not applied, ask for the credit memo.
6. For each Mismatch, compare the invoice with the PO and receipt.
7. Write the note in column J, and carry open notes to next month's workbook.

When the template stops scaling

The formulas are the quick part. The slow parts are getting the statement into the Statement tab (many arrive as PDFs that do not paste into columns) and asking the vendors who have not sent one. With a handful of vendors that is an hour or two. With a hundred, it is somebody's week.

Statement Match runs the same comparison without the copying. You upload your open-items and payments exports once a month, and it maps the columns the first time and remembers them. It emails each key vendor for its statement, reads PDF, Excel and CSV statements, and you check the lines read from each one beside the file before they are matched. The result is the same exception list, one per vendor, with an Excel export. To see it on invented data first, open the sample month.