Bank Reconciliation in Excel with XLOOKUP and FILTER

📘 Excel for India Course · Lesson 7 of 7

Bank reconciliation answers one question: why does the balance in our books differ from the bank’s? The answer is a list of entries that appear on only one side.

1. Two clean tables

Books (from the Tally cleanup in Lesson 6) and Bank (from the bank statement), each with Date, Reference (cheque no. or UTR), Amount (signed). Format both as Tables: Books and Bank.

2. A matching key

Amount alone is not unique (many ₹5,000 payments). Combine amount with the reference, or with the date when there is no reference:

Key:  =ROUND([@Amount],2)&"|"&IF([@Reference]="",TEXT([@Date],"yyyymmdd"),[@Reference])

3. Handle duplicates

If two identical entries exist, number them so each matches only once:

Key2: =[@Key]&"#"&COUNTIFS(INDEX([Key],1):[@Key],[@Key])

4. Find the differences

In Books:  In bank?  =XLOOKUP([@Key2],Bank[Key2],Bank[Date],"NOT IN BANK")
In Bank:   In books? =XLOOKUP([@Key2],Books[Key2],Books[Date],"NOT IN BOOKS")

List them with FILTER:

=FILTER(Books,Books[In bank?]="NOT IN BANK","All matched")

5. The reconciliation statement

Balance as per books
+ Cheques issued but not yet presented
− Deposits recorded but not yet credited by bank
+/− Bank charges, interest, direct credits not yet in books
= Balance as per bank statement

If the final line equals the actual bank balance, you are reconciled. Entries “not in books” (bank charges, interest, NEFT receipts) usually need journal entries in Tally.

That completes the course — you can now handle the core monthly Excel work of an Indian accounts team.