📎 This article includes 1 downloadable practice file ↓
Input tax credit (ITC) can only be claimed for invoices your suppliers have reported. Every month, the purchase register in your books must be matched with GSTR-2B from the GST portal. Here is a reliable Excel method.
In this article
1. Get the two lists
- GSTR-2B: download the Excel file from the GST portal (Returns → GSTR-2B → Download). Use the B2B sheet.
- Books: export the purchase register from Tally/Busy with supplier GSTIN, invoice number, date, taxable value and tax amounts.
Put them on sheets named 2B and Books, each formatted as a Table (Ctrl+T).
2. Clean the matching fields
Invoice numbers are written differently by suppliers and accountants: “INV/21-22/045”, “inv-21-22-45”, “045”. Build a cleaned version in both tables:
=UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM([@[Invoice No]]),"/",""),"-","")," ",""),"\",""))Also =UPPER(TRIM([@GSTIN])) for GSTINs.
3. Build a match key
=[@[GSTIN Clean]] & "|" & [@[Invoice Clean]]4. Match both ways
In Books:
=IF(COUNTIF(Tbl2B[Key], [@Key]), "In 2B", "Missing in 2B")In 2B:
=IF(COUNTIF(TblBooks[Key], [@Key]), "In books", "Not in books")5. Compare amounts for matched invoices
=IFERROR([@[Total Tax]] - INDEX(Tbl2B[Total Tax], MATCH([@Key], Tbl2B[Key], 0)), "")Flag differences above ₹1 (rounding) with conditional formatting.
6. Read the result
| Status | Meaning | Action |
|---|---|---|
| Matched, same tax | All good | Claim ITC |
| Missing in 2B | Supplier has not filed / filed wrongly | Follow up with vendor; defer ITC as per rules |
| Not in books | Invoice not booked, or booked under wrong GSTIN | Check with purchase team |
| Tax difference | Rate or value mismatch | Compare invoice copy; ask for credit/debit note |
7. Vendor follow-up list
=FILTER(TblBooks[[Supplier]:[Total Tax]], TblBooks[Status]="Missing in 2B", "All matched")(Excel 365). In older Excel, filter the Status column and copy. A pivot by supplier shows who owes you the most ITC.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗GST reconciliation practice workbookPurchase register vs GSTR-2B with planted problems (trailing spaces, a tax mismatch, a missing and an extra invoice), a ready Recon sheet and PASS/FAIL checks.⬇ XLSX · 12 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.