✍️ Author: Amrut Chitragar   |   📅 Published on: January 12, 2023   |   🛠 Last Updated: July 9, 2025

---

📌 This post is for educational purposes only. Always consult a SEBI-registered advisor before making financial decisions.
👉 Full disclaimer here

📌 TL;DR – Quick Summary:
GSTR-2B mismatch is not just about errors—it’s about visibility. Learn how to create a smart reconciliation register in Excel with formulas, carry-forward tracking, and supplier follow-up status. This guide gives you a practical, human approach to solving GST ITC issues—not just theory.

📚 Table of Contents

📚 Table of Contents (Click to Expand)

• 🔍 The Real Problem
• ✅ Your Solution: Smart ITC Register
• 💻 Excel Formulas You Can Use
• 📌 Pivot Table Method
• 📌 VLOOKUP Method
• 📌 Power Query Method
• 🔁 Track ITC Carry Forward
• ⚠️ Common Mistakes
• ❓ FAQ

How to Reconcile GSTR 2B with Books — And Actually Solve ITC Mismatch Problems

📌 Excel Visualization: GSTR-2B vs Books ITC Reconciliation with Pivot Table, Power Query & VLOOKUP

Reconciling GSTR-2B with your purchase register isn’t just a matching exercise—it's a survival skill for modern accountants. Let's solve this with a real-world, practical approach that you can implement today.

🔍 The Real Problem

• GSTR-2B shows invoices you never received—or amounts that don’t match.
• Suppliers forget to upload invoices or use wrong GSTIN.
• Pending ITC piles up every month, but there’s no system to track it.

✅ Your Solution: Smart ITC Register

Create a master Excel sheet that does more than just match. It should:

• Match every invoice with status: Matched / Mismatch / Not in 2B / Not in Books
• Track Pending ITC Month-wise with reasons
• Use formulas to flag supplier-wise issues

↑ Back to TOC

💻 Excel Formulas You Can Use

• =VLOOKUP() – to match invoices across sheets
• =IFERROR() – to hide errors when lookup fails
• =COUNTIF() – to check how many invoices are unmatched
• =MATCH() – to verify if invoice exists across lists
• =FILTER() – to auto-list pending ITC or mismatches (Excel 365)

↑ Back to TOC

📌 Pivot Table Method

📌 Pivot Table Method – Excel-Style Reconciliation

Here's how I use Pivot Table to reconcile GSTR-2B and Books in Excel. The logic is clear, supplier-wise, and easy to follow.

✅ Step-by-Step:

• Download GSTR-2B and ITC Register.
• Merge both with a column "Source" → Books / GSTR-2B.
• Create Pivot Table → Rows: GSTIN, Columns: Source, Values: ITC Amount.
• Add Difference field → Books – GSTR-2B

📋 Sample Combined Data:

GSTIN	Invoice No	Invoice Date	Taxable Value	ITC Amount	Source
27AAAAA0000F1Z5	INV001	2025-06-01	₹10,000	₹1,800	Books
27BBBBB0000F1Z6	INV002	2025-06-03	₹12,000	₹2,400	Books
27CCCCC0000F1Z7	INV003	2025-06-05	₹20,000	₹3,600	Books
29DDDDD0000F1Z8	INV004	2025-06-10	₹25,000	₹5,000	Books
29EEEEE0000F1Z9	INV005	2025-06-12	₹10,000	₹1,800	Books
27AAAAA0000F1Z5	INV001	2025-06-01	₹10,000	₹1,800	GSTR-2B
27BBBBB0000F1Z6	INV002	2025-06-03	₹12,000	₹0	GSTR-2B
27CCCCC0000F1Z7	INV003	2025-06-05	₹20,000	₹0	GSTR-2B
29DDDDD0000F1Z8	INV004	2025-06-10	₹25,000	₹5,000	GSTR-2B
29EEEEE0000F1Z9	INV005	2025-06-12	₹10,000	₹1,200	GSTR-2B

📊 Pivot Table Fields:

PivotTable Fields

Choose fields to add to report:

GSTIN

Invoice No

ITC Amount

Source

MORE TABLES...

FILTERS

COLUMNS

Source

ROWS

GSTIN

VALUES

ITC Amount

📈 Final Pivot Table Output:

GSTIN	Books ITC	GSTR-2B ITC	Difference (Books – 2B)
27AAAAA0000F1Z5	₹1,800	₹1,800	₹0
27BBBBB0000F1Z6	₹2,400	₹0	₹2,400
27CCCCC0000F1Z7	₹3,600	₹0	₹3,600
29DDDDD0000F1Z8	₹5,000	₹5,000	₹0
29EEEEE0000F1Z9	₹1,800	₹1,200	₹600

🎯 Final Step: Create Annexure-1 and Annexure-2 using the difference list. Net mismatch should be ₹0 before 3B filing.

↑ Back to TOC

📌 VLOOKUP Method

📌 VLOOKUP Method – Manual Reconciliation in Excel

If you prefer formula-based reconciliation instead of Pivot Table, here's how I use VLOOKUP to match Books vs GSTR-2B records invoice-wise.

✅ Step-by-Step:

• Prepare your Books and GSTR-2B in separate sheets (same column headers).
• In Books sheet, insert a new column: GSTR-2B ITC.
• Apply VLOOKUP formula based on Invoice No or GSTIN+Invoice No as unique key.
• Formula Example:
  =IFERROR(VLOOKUP(B2, '2B Sheet'!B:F, 5, FALSE), 0)
• In next column, calculate:
  =ITC (Books) – ITC (GSTR-2B)
• Filter where difference ≠ 0 to find mismatches.

📋 Sample Books Data:

GSTIN	Invoice No	Invoice Date	Taxable Value	Books ITC
27AAAAA0000F1Z5	INV001	2025-06-01	₹10,000	₹1,800
27BBBBB0000F1Z6	INV002	2025-06-03	₹12,000	₹2,400
27CCCCC0000F1Z7	INV003	2025-06-05	₹20,000	₹3,600
29DDDDD0000F1Z8	INV004	2025-06-10	₹25,000	₹5,000
29EEEEE0000F1Z9	INV005	2025-06-12	₹10,000	₹1,800

📋 Sample GSTR-2B Data:

GSTIN	Invoice No	Invoice Date	Taxable Value	2B ITC
27AAAAA0000F1Z5	INV001	2025-06-01	₹10,000	₹1,800
27BBBBB0000F1Z6	INV002	2025-06-03	₹12,000	₹0
27CCCCC0000F1Z7	INV003	2025-06-05	₹20,000	₹0
29DDDDD0000F1Z8	INV004	2025-06-10	₹25,000	₹5,000
29EEEEE0000F1Z9	INV005	2025-06-12	₹10,000	₹1,200

📈 Final Reconciliation Output:

Invoice No	Books ITC	GSTR-2B ITC	Difference
INV001	₹1,800	₹1,800	₹0
INV002	₹2,400	₹0	₹2,400
INV003	₹3,600	₹0	₹3,600
INV004	₹5,000	₹5,000	₹0
INV005	₹1,800	₹1,200	₹600

🎯 Final Step: Highlight mismatches and take follow-up actions using the difference column. This method gives full control and flexibility over the data.

↑ Back to TOC

📌 Power Query Method

📌 Power Query Method – Automated Reconciliation in Excel

If you're reconciling GSTR-2B monthly and want to avoid repeating manual work, this Power Query method is ideal. Just replace source files each month and refresh!

✅ Step-by-Step:

• Open Excel → Go to Data → Get Data → From Workbook.
• Load Books.xlsx and GSTR2B.xlsx into Power Query.
• Ensure column names like GSTIN, Invoice No, ITC Amount are aligned.
• Merge Queries using Invoice No or GSTIN + Invoice as key.
• Choose Left Outer Join (Books to GSTR-2B).
• Expand GSTR-2B ITC into the Books table.
• Create a new column: Diff = Books ITC - GSTR-2B ITC
• Load the result into Excel Sheet.

📋 Sample Result after Merge:

Invoice No	Books ITC	2B ITC	Difference
INV001	₹1,800	₹1,800	₹0
INV002	₹2,400	₹0	₹2,400
INV003	₹3,600	₹0	₹3,600
INV004	₹5,000	₹5,000	₹0
INV005	₹1,800	₹1,200	₹600

🧠 Why Use Power Query:

• One-time setup → reusable every month
• No formulas, no manual copy-paste
• Ideal for 100+ invoices, multiple GSTINs
• Merge, clean, transform all in one step

🎯 Final Tip: Save this as a template workbook. Every month, just replace Books and 2B files and click Refresh All.

↑ Back to TOC

🔁 Track ITC Carry Forward

Use a simple “Pending ITC Register” that tracks:

• Carry Forward from last month
• Resolved or Still Pending?
• Reason: Supplier Filed Late / Wrong GSTIN / Dispute

⚙️ Power Query for Automation

If you’re handling large volumes, use Power Query to:

• Import GSTR-2B from JSON or Excel
• Merge with Purchase Register
• Auto-refresh mismatch report every month

↑ Back to TOC

⚠️ Common Mistakes in GSTR-2B Reconciliation

• ✅ Wrong or incomplete GSTIN used in supplier invoice
• ✅ Invoice numbers don't match due to extra spaces or special characters
• ✅ ITC booked in books but supplier hasn’t filed GSTR-1
• ✅ Using wrong month’s GSTR-2B for comparison
• ✅ Not adjusting debit notes / credit notes properly
• ✅ Treating B2C entries as B2B, leading to mismatch
• ✅ Ignoring blocked ITC under Rule 42 or ineligible expenses

↑ Back to TOC

❓ Frequently Asked Questions (FAQ)

Q1. How often should I reconcile GSTR-2B?
Ideally, reconcile GSTR-2B every month before filing GSTR-3B. For high-volume businesses, fortnightly review is recommended.

Q2. What if the supplier files invoice in wrong GSTIN or wrong month?
You need to follow up with the supplier for correction. If the invoice appears in a future GSTR-2B, claim ITC in that month only.

Q3. Can I claim ITC if it is not appearing in GSTR-2B?
No. As per Rule 36(4), ITC must reflect in GSTR-2B to be eligible for claim, except in special cases like imports or ISD.

Q4. Do I need to prepare Annexure-1 and Annexure-2 every month?
Yes, it's a good practice. It helps you track follow-up status and pending ITC clearly supplier-wise.

Q5. Can I automate reconciliation?
Yes. Use Power Query in Excel to build a reusable monthly system that refreshes with updated files.

↑ Back to TOC

📥 Need the Excel format shown above?
👉 Click here to download from our GSTR-3B ITC Register Guide — ready-to-use Excel file with Books vs 2B Reco format.

🎯 Final Tip: Focus on Action, Not Just Matching

Reconciliation isn’t about perfection—it’s about control. When you have a real system in Excel, you gain confidence in your ITC claim, and even if there’s an audit—you’re ready.

Need help building this in your firm? I'm preparing a full video guide + downloadable Excel. Stay tuned!

📺 Want to see how this Excel works in real-time?
👉 Subscribe to our YouTube channel – Learn with Amrut for tutorials, automation, and real GST use-cases.

✉️ Need help or consulting?
💬 Click here to contact me and get your process reviewed.