How to reconcile a vendor statement in Excel (step by step)

Excel is where most accounts-payable teams start with vendor statement reconciliation. It is free, familiar, and good enough for a handful of clean lines. This guide walks through the exact spreadsheet method — setup, formulas, and how to find every type of exception — then shows where the approach breaks and what to do about it.

If you only take one thing away: the formulas are the easy part. The real work in Excel reconciliation is getting two differently-formatted files into a state where the formulas can actually match them.

Step 1 — Set up your two sheets

Put each source on its own sheet, one row per line item, with clean, consistent columns. A simple layout is: Invoice Number, Date, Amount.

• Sheet 1 — Statement. The vendor's statement. If it arrived as a PDF you will need to copy the table out; if it is a scan, you may have to re-type it or run OCR first.
• Sheet 2 — Ledger. Your AP ledger or aged-payables export for that vendor and period.

Clean the data first

• Remove currency symbols and thousands separators so amounts are plain numbers.
• Store invoice numbers as text to preserve leading zeros (000123 must not become 123).
• Trim hidden spaces with =TRIM(A2) — a trailing space is the most common reason two identical-looking numbers will not match.
• Make invoice-number casing consistent with =UPPER(A2) if the vendor mixes cases.

Step 2 — Match each line with XLOOKUP

On the Statement sheet, pull the matching ledger amount next to each line using the invoice number as the key:

=XLOOKUP(A2, Ledger!A:A, Ledger!C:C, "MISSING")

Then add a Variance column to compare the statement amount with the ledger amount:

=B2 - XLOOKUP(A2, Ledger!A:A, Ledger!C:C, 0)

If your Excel does not have XLOOKUP, the VLOOKUP equivalent is:

=VLOOKUP(A2, Ledger!A:C, 3, FALSE)

Any row showing MISSING is on the statement but not in your ledger. Any row with a non-zero variance is an amount mismatch.

Step 3 — Find every exception type

Missing in ledger (on statement, not in books)

These are the MISSING results from the lookup above. Filter the Variance/lookup column for MISSING to list them.

Missing in statement (in books, not on statement)

Run the lookup the other way: on the Ledger sheet, look up each ledger invoice in the Statement sheet. Anything that returns MISSING is in your books but absent from the statement.

=XLOOKUP(A2, Statement!A:A, Statement!C:C, "MISSING")

Amount mismatches

Filter the Variance column for any value that is not zero. Sort by absolute variance to deal with the largest discrepancies first.

Duplicate invoices

Add a column with =COUNTIF(A:A, A2) — any result greater than 1 means the invoice number appears more than once. Note this only catches exact duplicates; it will miss the same charge under a slightly different number.

Unapplied credits

Filter the statement for negative amounts (credit notes) that returned MISSING from the ledger lookup — those are credits you may be owed but have not recorded or applied.

A worked example

Suppose the statement lists invoice INV-1001 at 1,250.00 and your ledger has INV-1001 at 1,205.00. The lookup finds a match, and the variance column shows 45.00 — an amount mismatch, most likely a transposition (1,250 vs 1,205) or a tax difference. Meanwhile INV-1007 appears on the statement but returns MISSING — you never booked it. And COUNTIF flags INV-0990 twice — a duplicate. In a few formulas you have isolated three real issues to investigate, while the dozens of clean lines fall away.

Why Excel reconciliation breaks down

The method above is sound, but in practice the spreadsheet approach struggles the moment real-world files arrive:

• PDFs and scans. Most statements arrive as PDFs — often scanned — so the data has to be copied out or re-typed before any formula can touch it. This is slow and introduces typos.
• Format drift. Every vendor labels columns differently and orders them differently, so you re-build the layout for each statement.
• Invoice-number mismatches. Leading zeros, prefixes, and spacing cause lookups to silently return MISSING for invoices that actually match.
• Fragile formulas. One inserted column, sorted range, or mis-paste quietly breaks a VLOOKUP and skews the totals — with no warning and no audit trail.
• No scale. A few statements a month is manageable; dozens becomes hours of repetitive, error-prone work.

Tips to make Excel reconciliation less painful

• Always TRIM and standardise invoice numbers before matching.
• Use XLOOKUP with a clear default (MISSING) so unmatched lines are obvious.
• Build the reconciliation as a reusable template so you are not starting from scratch each month.
• Add conditional formatting to highlight non-zero variances and COUNTIF duplicates automatically.

When to move to software

If you are spending more than a few minutes per statement, handling scanned PDFs, or reconciling more than a handful of vendors, dedicated software pays for itself quickly. VendorRecon extracts and normalises both files for you, matches every line by invoice ID and amount — tolerating OCR and formatting differences that break Excel lookups — and returns only the exceptions, ready to export back to Excel.

Frequently asked questions

Should I use VLOOKUP or XLOOKUP?

Use XLOOKUP if your Excel version has it — it is simpler, can return a default like MISSING, and does not break when columns move. VLOOKUP works too but is more fragile.

Why do my invoice numbers not match even though they look the same?

Usually leading zeros, a text-vs-number mismatch, hidden spaces, or a prefix the vendor adds. Store invoice numbers as text and trim spaces before matching.

How do I find duplicates in Excel?

Use =COUNTIF(range, cell) on the invoice-number column; any result greater than 1 is a duplicate. This finds exact duplicates only, not same-amount-different-number ones.

Is Excel good enough for vendor statement reconciliation?

For a few low-volume, clean files, yes. It breaks down with scanned PDFs, mismatched formats, and higher volumes, where dedicated software is far faster and more reliable.