To reconcile a vendor statement in Excel, put your ledger and the supplier's statement on two sheets with the same columns (date, document number, amount), clean the document numbers so they look the same on both sides, then use a matching key and COUNTIFS or XLOOKUP to flag lines that exist on only one side. The unmatched lines, added up, must equal the balance difference.
1. Prepare both lists
- Sheet “Ours”: your vendor ledger export (open and cleared items for the period).
- Sheet “Theirs”: the supplier's statement, converted from PDF to Excel if necessary.
- Same columns on both: Date, Document no., Description, Amount. Make the supplier's signs match yours (their debit is your credit).
2. Clean the document numbers
Most mismatches are formatting: prefixes, leading zeros, spaces or dashes. Add a key column that removes them, for example =UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"-","")," ","")). For payments, which often have no shared number, use amount plus date as the key.
3. Match and flag
- In “Ours”, add =COUNTIFS(Theirs!E:E,E2,Theirs!D:D,D2) to see whether the same key and amount exist on the supplier side.
- Do the same on “Theirs” against “Ours”.
- Filter for 0: these are the lines that explain the difference.
- Check that the sum of unmatched lines on both sides equals the balance difference; if not, look for amount differences on matched keys.
Where Excel stops working
Excel is fine for one supplier with a few dozen lines. It breaks down with hundreds of lines, numbers written in many formats, partial payments, foreign currency and dozens of suppliers at year end. mutabakat.com reads both exports as they are, matches them with a rule engine supported by trained AI, and an expert checks the result before it reaches you.
Frequently asked questions
Which Excel formula is best for reconciliation?
COUNTIFS to flag missing lines and SUMIFS to compare amounts per document. XLOOKUP is useful to bring the other side's amount next to yours.
How do I reconcile payments without a reference?
Match on amount and a date window (for example ±5 days), then confirm manually; payment dates often differ between payer and receiver.
Start with one account. If the differences are not clearer than in your spreadsheet, you have lost nothing.
Start free trial →Last updated: November 14, 2026 · mutabakat.com