Debtor/Creditor Ledger Reconciliation in Excel

Q: How do I match two ledgers in Excel?

Use `XLOOKUP` (or `VLOOKUP`) on a common key like the invoice number to flag entries that appear in one ledger but not the other, then add an amount check to catch matched entries with different values.

Q: Is the template free?

Yes — free to download, no sign-up. ---

Last updated: 27 June 2026 · Matching method, formulas and a free template.

Ledger reconciliation in Excel means putting your ledger for a party next to their ledger for you, matching the two entry by entry, and explaining every difference — missing invoices, unrecorded payments, wrong amounts or timing gaps — until both closing balances tie out. The workhorse is a lookup (XLOOKUP or VLOOKUP) that flags which entries appear on one side but not the other.

Key takeaways

Reconcile = match + explain. Line up both ledgers and account for every difference, not just the net.
Match on a key — invoice/voucher number is the most reliable; amount and date help confirm.
XLOOKUP/VLOOKUP flags entries present in one ledger but missing in the other.
Common differences: missing entries, amount mismatches, timing/cut-off, debit/credit notes, TDS deducted by the other side.
A short reconciliation statement lists each difference and reconciles their closing balance to yours.
Start from clean party ledgers — reconciliation is only as good as the ledger underneath.

Fact box. A ledger reconciliation is not done when the net difference is zero — it's done when every individual difference is identified and explained. Two ledgers can show the same closing balance with offsetting errors hiding inside.

What is ledger reconciliation?

Ledger reconciliation compares your books for a party with that party's books for you and resolves why they differ. Your "Sharma Traders" ledger might show ₹2,40,000 outstanding while Sharma's books show ₹2,05,000 — reconciliation finds the ₹35,000 worth of reasons (an invoice they haven't booked, a payment you missed, a TDS deduction, a credit note) so both sides agree.

It's essential before year-end, before sending or accepting a balance confirmation, and whenever a party disputes what's outstanding.

Why do two ledgers differ? (common reasons)

Difference	What it means
Missing entry	One side recorded an invoice/payment the other hasn't booked yet
Amount mismatch	Same invoice, different value (rate, rounding, freight, GST)
Timing / cut-off	A March entry on one side falls in April on the other
Debit / credit notes	A return or discount booked on one side only
TDS deducted	Customer recorded payment net of TDS; you booked the gross
Duplicate	The same bill entered twice on one side

Most reconciliation differences trace back to one of these six — knowing the list makes them faster to spot.

Fact box. A very frequent SMB reconciliation gap is TDS: your customer pays you the invoice amount minus TDS and records the full invoice as settled, while your ledger still shows the TDS portion outstanding. It's a difference to explain, not chase.

How do I reconcile two ledgers in Excel with XLOOKUP?

Put both ledgers in one workbook — yours and theirs — each with a common key (invoice/voucher number) and an amount column.

Clean the key column on both sides so references match exactly (=TRIM(UPPER(ref)) removes stray spaces/case).
In your ledger, add a "In their books?" column: =IFERROR(XLOOKUP([Ref], TheirRefColumn, TheirAmountColumn), "MISSING") MISSING = an entry they haven't booked.
In their ledger, add the mirror column looking up against yours — MISSING there = an entry you haven't booked.
Flag amount mismatches where the ref matches but values differ: =IF([YourAmount]=[TheirAmountFound], "OK", "DIFF")
Filter each side for MISSING and DIFF — that's your full list of differences to explain.

On older Excel without XLOOKUP, use VLOOKUP([Ref], TheirTable, amount_col, FALSE) wrapped in IFERROR(…, "MISSING") the same way.

How do I build the reconciliation statement?

Once the differences are listed, write a short statement that walks from their closing balance to yours:

Balance as per their ledger                     ₹2,05,000
Add: invoices not yet booked by them            +  30,000
Less: payment not yet booked by you             −   ... 
Add: TDS deducted by them, open in your books   +   ...
Add/Less: credit notes, timing differences      ±   ...
= Balance as per your ledger                    ₹2,40,000

Each line is one explained difference from your MISSING/DIFF list. When the adjusted figure equals your closing balance, the ledger is reconciled.

How to reconcile (step by step)

Get both ledgers for the same period and paste each into its own sheet.
Standardise the key (invoice/voucher no.) with TRIM/UPPER so lookups match.
Run XLOOKUP both ways to flag MISSING entries on each side.
Add the amount-mismatch check for refs that exist on both sides.
Filter for MISSING and DIFF and list every difference.
Classify each (missing, timing, TDS, note, duplicate) and build the reconciliation statement.
Confirm both closing balances tie after the adjustments.

A free ledger-reconciliation template is available to download, no sign-up — paste both ledgers and the lookup columns flag the differences for you.

How Ankeshan helps: Ankeshan matches your party ledger against the other side's inside Excel — it flags missing invoices, amount mismatches and TDS gaps, and drafts the reconciliation statement, so balance confirmations take minutes. It's launching soon; join the waitlist.

Frequently asked questions

What is ledger reconciliation? It's the process of matching your ledger for a party against their ledger for you, identifying every difference (missing entries, amount or timing mismatches, TDS, notes) and explaining it until both closing balances agree.

How do I match two ledgers in Excel? Use XLOOKUP (or VLOOKUP) on a common key like the invoice number to flag entries that appear in one ledger but not the other, then add an amount check to catch matched entries with different values.

Why does my ledger differ from my customer's by the TDS amount? Customers often pay the invoice net of TDS and record the invoice as fully settled, while your books still show the TDS portion open. It's a difference to reconcile and adjust against the TDS credit, not a payment to chase.

Is net-zero difference enough? No. Two ledgers can show the same balance while errors offset each other inside. Reconcile every individual difference, not just the net total.

Is the template free? Yes — free to download, no sign-up.

Sources

Microsoft Excel function reference — XLOOKUP, VLOOKUP, IFERROR, TRIM, UPPER: support.microsoft.com.
General accounting practice for ledger reconciliation and balance confirmation.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.