How to reconcile a supplier statement in Excel

Excel is a capable tool for reconciliation. A typical workflow uses XLOOKUP or VLOOKUP, filters, duplicate checks and formulas to compare statement and ledger exports.

Reconcile a supplier statement

The familiar Excel approach

Standardise reference columns, look up each invoice in the other list, compare gross values, filter missing results, check duplicates and repeat the lookup in the opposite direction.

Where manual work becomes awkward

Column names differ between exports, references can follow supplier-specific conventions and repeated formulas are easy to misapply. Reviewing both directions often creates several helper columns.

Use your spreadsheets without rebuilding them

tallyvero accepts the files you already use, lets you map their columns and creates a structured exception view. It complements Excel rather than replacing the spreadsheet work your team values.

A practical Excel workflow

Place the supplier statement and purchase ledger data into suitable worksheets.
Identify and standardise the reference and signed amount columns.
Use XLOOKUP or another appropriate lookup method to locate each reference on the other sheet.
Compare the amounts for references found on both sides.
Filter references that were not found and repeat the check in the other direction.
Check duplicate references and credit notes separately.
Investigate each exception against source records.

When a dedicated comparison helps

Excel remains useful for investigation. If you repeat the setup often, tallyvero performs the two-way comparison directly from your exports without another formula workbook. It keeps duplicate groups ambiguous instead of choosing a convenient row.