Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
The supplier's statement shows a different balance from the supplier ledger, and nobody can say which entries cause the gap.
Place both lists side by side, look up each document number from one list in the other, and classify each unmatched line as an unbooked invoice, payment in transit, credit or debit note, TDS or rate difference until the balances agree.
Your ledger extract and the vendor's statement for the same period, each with date, document number and amount, a cleaned key column, a lookup column and a reconciliation summary.
A one-page reconciliation that starts with your balance, adds and subtracts each reconciling item, and ends at the vendor's balance.
Put your ledger and the vendor’s statement side by side. Match them line by line on document number and amount. List each line that does not match as a reconciling item: an invoice you have not booked, a payment in transit, a credit note, TDS or a rate difference. When the items explain the gap, the reconciliation is done.
The lookup formula below uses XLOOKUP (Microsoft 365, Excel 2021 and later); an INDEX-MATCH version for older Excel is included.
Which reconciling items appear, and on which side?
| Item | Shows in | Effect on the gap | ₹ example |
|---|---|---|---|
| Invoice not booked by you | Vendor statement only | Vendor balance higher | ₹60,000 invoice dated 28 Sep, not yet received at your office |
| Payment in transit | Your ledger only | Vendor balance higher | ₹75,000 paid by NEFT on 30 Sep, credited to vendor on 1 Oct |
| Credit note not booked by you | Vendor statement only | Vendor balance lower | ₹5,200 credit note for short supply |
| Debit note not booked by vendor | Your ledger only | Vendor balance higher | ₹8,000 debit note for rejected goods |
| TDS deducted by you | Your ledger shows full invoice cleared | Vendor balance higher | ₹1,00,000 invoice paid ₹98,000 after ₹2,000 TDS; vendor still shows ₹2,000 open |
| Rate or quantity difference | Both, with different amounts | Either way | Invoice ₹1,18,000 at vendor, ₹1,15,640 in your books at the PO rate |
The TDS line is the most common surprise. Your books cleared the ₹1,00,000 invoice with ₹98,000 paid and ₹2,000 TDS payable. The vendor sees only ₹98,000 received. Until they see the ₹2,000 in their tax credit statement and book it, their statement shows ₹2,000 still due.
What layout should the workbook have?
Three sheets:
- Ledger: your supplier ledger for the period. Columns: date, document number, description, debit, credit, cleaned key.
- Statement: the vendor’s statement, in the same columns.
- Recon: the summary that starts with your balance and ends at theirs.
The cleaned key removes spaces, slashes and hyphens so “INV/2214” and “INV-2214” match:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"-",""),"/","")," ",""))How do I match the two lists with a lookup?
On the Ledger sheet, with the cleaned key in F and amount in E, fetch the vendor’s amount for the same document:
=XLOOKUP(F2, Statement!$F:$F, Statement!$E:$E, "Not in statement")For Excel 2019 or earlier:
=IFERROR(INDEX(Statement!$E:$E, MATCH(F2, Statement!$F:$F, 0)), "Not in statement")Add a status column:
=IF(G2="Not in statement", "Missing at vendor", IF(ABS(G2-E2)<=1, "Matched", "Amount differs"))Then do the same on the Statement sheet, looking up into Ledger, with “Not in ledger” as the fallback and “Missing in books” as the status, to find lines missing from your books. Filter both sheets for anything other than “Matched”. Those lines are your reconciling items.
Worked example: ₹4,26,500 against ₹4,81,300
Your ledger shows ₹4,26,500 payable to a packaging supplier at 30 September. The supplier’s statement shows ₹4,81,300. The gap is ₹54,800.
After the lookups, two lines on the statement show “Missing in books” and nothing is missing at the vendor’s end:
| Recon line | Amount |
|---|---|
| Balance as per our ledger | ₹4,26,500 |
| Add: invoice SP/1190 dated 28 Sep, not booked by us | ₹60,000 |
| Less: credit note CN/77 for short supply, not booked by us | (₹5,200) |
| Balance as per vendor statement | ₹4,81,300 |
Check: ₹4,26,500 plus ₹60,000 is ₹4,86,500. Less ₹5,200 is ₹4,81,300. That matches the vendor, so the gap is fully explained.
Next steps: get the invoice from the vendor, check it against the goods receipt and book it; then book the credit note. After that your ledger shows ₹4,81,300 too.
How often should I ask for statements, and what should I send?
A common practice:
- Top 20 suppliers by spend: monthly.
- Others with a balance: quarterly.
- Everyone with a balance: at year-end, before the books close.
When you ask, send your ledger extract for the same period, your closing balance, and the list of TDS you deducted with the dates you deposited it. That lets the vendor reconcile from their side too, and most differences close in one email.
What to read next
Next, read debtors and creditors reconciliation for matching many parties at once, and invoice matching for TDS net versus gross for the TDS line. To compare many vendors in one view, see how to use a pivot table to find the difference between two ledgers. Related: account reconciliation software for India.