A. Find missing invoices in GSTR-2A (vendor not filed)
In Purchase Register sheet, add a column: In GSTR-2A?
=IF(COUNTIFS(GSTR2A!B:B, B2, GSTR2A!C:C, C2, GSTR2A!D:D, D2)>0, "Yes", "No")
(B2 = Supplier GSTIN, C2 = Invoice No., D2 = Date)
Filter “No” – these are invoices where ITC cannot be claimed. gstr 2a and 3b reconciliation in excel format download
B. Find amount mismatches
Add column: Difference in Taxable Value
= ABS(Purchase_Reg_Taxable - VLOOKUP(Invoice_No, GSTR2A_Range, Column_Index, 0))
Better to use SUMIFS for multi-condition match. (B2 = Supplier GSTIN, C2 = Invoice No
C. Compare ITC claimed (3B Table 4) vs ITC available (2A)
| ITC Type | Available in 2A (sum) | Claimed in 3B | Difference |
|----------|----------------------|---------------|------------|
| IGST | =SUMIFS(...) | Manual entry | Available - Claimed |
| CGST | | | |
| SGST | | | |
| Issue | Excel Fix |
|-------|------------|
| Supplier changed GSTIN | Use fuzzy matching or manually verify |
| Invoice date format mismatch | Use DATEVALUE and standardize |
| Duplicate entries in GSTR-3B | Use Remove Duplicates based on invoice number + GSTIN |
| IGST treated as CGST+SGST | Create helper column to normalize tax type |
| Missing debit/credit notes | Append separate sheet for amendments | Better to use SUMIFS for multi-condition match
| Columns | Description | |---------|-------------| | GSTIN of Supplier | | | Invoice No. | | | Invoice Date | | | Taxable Value | | | CGST Claimed | | | SGST Claimed | | | IGST Claimed | | | Total ITC Claimed | | | Month | |
If you are looking to download a file, reliable sources typically include: