Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
A mid-market Indian enterprise runs GSTR-2B input tax credit reconciliation by pulling a purchase register from the ERP, pulling a GSTR-2B extract from the GST portal, and running a two-way match in Excel. Since the Invoice Management System went live on 14 October 2024, the recipient action log has become a third input — every inbound document requires an Accept, Reject, or Keep Pending action before the monthly cycle closes, and the default action if no action is taken is Accept. A two-way workbook misses two classes of failure the IMS regime introduced: valid invoices wrongly rejected on IMS that then never reach the ITC ledger, and documents defaulted to Accept because no action was taken. Add the Section 16(4) November 30 permanent-loss deadline running on every unfiled supplier row, the Rule 37 180-day payment clock running on every paid supplier row, and the Rule 37A September 30 supplier-filing clock running behind them, and the Excel discipline that used to work needs a re-engineered workbook that operationalises the three-way match, the five-bucket categorisation, and the at-risk queue as first-class outputs.
Build the workbook around three input sheets keyed on a normalised composite key of supplier GSTIN, invoice number stripped of prefix and suffix noise, invoice date, and taxable-value amount within a tolerance band. Sheet 1 is the ERP purchase register with those four keys plus the IGST/CGST/SGST split, the HSN or SAC code, the payment status and payment date (for Rule 37 ageing), and a Section 17(5) blocked-flag column. Sheet 2 is the GSTR-2B extract copy-pasted from the JSON-to-Excel converter the GST portal ships, with the supplier's GSTIN, invoice number, date, taxable value, and tax split. Sheet 3 is the IMS action log with GSTIN, invoice number, and the recorded IMS action (Accept, Reject, Pending, or Default-Accept). A driver sheet runs XLOOKUP with the composite key against the other two sheets and assigns one of five categorisation buckets to every row — Matched, Supplier not filed, Ghost invoice, IMS reject, IMS pending. Two computed sheets fall out — the at-risk queue keyed to the Section 16(4) deadline with a days-to-deadline countdown, and a supplier follow-up list keyed to unique supplier GSTIN with aggregated exposure. Formulas use XLOOKUP with an exact match, TEXTBEFORE and TEXTAFTER to strip GST invoice-number prefixes and suffixes for normalisation, ROUND to a paise tolerance for amount matching, and TODAY() with an EOMONTH-derived November 30 anchor for the countdown.
Purchase register sheet with columns for invoice number, invoice date, supplier GSTIN, supplier PAN, recipient GSTIN, taxable value, IGST, CGST, SGST, Cess, HSN or SAC code, Section 17(5) blocked flag, payment status, payment date, and remarks. GSTR-2B extract sheet with the same key columns pulled from the portal-generated Excel. IMS action log sheet with invoice number, GSTIN, and action code (A, R, P, or DA for Default-Accept). Supplier master sheet with GSTIN, supplier name, contact email, contact phone, and Rule 37A supplier-filing status. Configuration cell for the applicable financial year (used to derive the Section 16(4) target date). Configuration cell for the amount tolerance in rupees (typically 1 rupee absorbable, 10 rupees flagged). Named ranges on every input sheet so the driver formulas reference stable names rather than volatile cell ranges.
A driver sheet with one row per purchase register invoice, categorised into one of five buckets and colour-banded. An at-risk queue that filters the Supplier-not-filed bucket and shows days-to-deadline against 30 November, banded into over-90-days-informational, 61-to-90-days-Tier-1, 31-to-60-days-Tier-2, and under-30-days-Tier-3. A supplier follow-up list with unique supplier GSTIN, count of unfiled invoices, total exposure in rupees, and a contact placeholder ready for mail-merge. An IMS action queue that surfaces the Pending and Default-Accept rows for the current cycle. A Section 17(5) exclusion list. A reconciled ITC total that populates GSTR-3B Table 4 for the tax period. Every sheet refreshes on open, so a Monday morning refresh reflects the exposure state the finance team is walking into that week.
Every Indian finance team that runs its own GSTR-2B input tax credit reconciliation in Excel has some version of the same workbook — a purchase register on one tab, a GSTR-2B extract on another, and a VLOOKUP that ties them together. Since 14 October 2024, that workbook has been one input short. The Invoice Management System introduced a third input — the recipient action log — and the two-way match that used to work is missing two entire classes of failure the new regime introduced. This is the recipe for a re-engineered Excel workbook that runs the three-way ITC reconciliation Excel India finance teams need: purchase register versus GSTR-2B versus IMS action log, with a five-bucket categorisation, an at-risk queue that counts down to the Section 16(4) November 30 permanent-loss deadline, a supplier follow-up list that generates from the exceptions, and formulas explained at the level a senior AP or indirect tax executive can lift and adapt. It is the working companion to the reconciliation playbook cluster and, on the design side, to the GSTR-2B failure mode analysis that catalogues exactly what this workbook is engineered to catch.
Why the two-way Excel workbook no longer reconciles
Before the Invoice Management System went live, the GSTR-2B ITC reconciliation had two inputs. The buyer’s own purchase register — what invoices the buyer believed it had received. And the auto-generated GSTR-2B — what the portal published on the fourteenth of the following month based on suppliers’ GSTR-1 filings. A two-way match keyed on supplier GSTIN, invoice number, invoice date, and taxable value produced a clean four-bucket categorisation: matched invoices flowed to GSTR-3B Table 4; buyer-only invoices flagged supplier non-filing and entered an at-risk queue against the Section 16(4) deadline; portal-only invoices flagged either a missed booking or a ghost invoice; and the residual bucket handled the rounding and tolerance cases.
The IMS regime added a fourth pathway. Every inbound document a supplier files against the buyer’s GSTIN now lands on the buyer’s IMS dashboard in a Pending status. The buyer takes one of three actions — Accept, Reject, or Keep Pending — before the monthly IMS cycle closes on the fourteenth. Only Accepted documents flow into GSTR-2B; Rejected documents are excluded; Pending documents defer to the next cycle. If the buyer takes no action, IMS defaults the action to Accept when the cycle closes.
This introduces two failure classes that a two-way workbook cannot see. A valid invoice can be wrongly Rejected on IMS by an inattentive AP executive — the invoice sits correctly in the purchase register but never appears in GSTR-2B because the IMS layer excluded it, and the Section 16(4) clock starts running on a legitimate credit the team believed was already claimed. A wrongly-issued invoice can be defaulted to Accept because no one actioned the IMS dashboard before the cycle closed, and the ghost credit flows into GSTR-2B without a purchase register counterpart — a Section 74 fraud-ITC recovery waiting to happen if the CBIC picks it up in an audit.
Both classes require the workbook to see the IMS action log as a first-class input alongside the purchase register and GSTR-2B, and to categorise every row against all three inputs together. The rest of this article is that recipe.
The three inputs
The workbook has three input sheets and one supplier master. Each input is copy-pasted from a portal or ERP download; no scraping, no API, no macro is required.
Input 1 — Purchase register from the ERP
Extract the purchase register for the tax period being reconciled. The columns the workbook needs are invoice number, invoice date, supplier GSTIN, supplier PAN, recipient GSTIN (the one under which the purchase was booked in a multi-GSTIN group), taxable value, IGST amount, CGST amount, SGST amount, Cess amount, HSN or SAC code, Section 17(5) blocked-flag (Yes or No — a computed flag based on the HSN or SAC and the payment description, covering motor vehicle repair, food and beverages, works contract on immovable property, life insurance where not mandatory, and personal-consumption items), payment status (Paid, Partly Paid, Unpaid), payment date (used for the Rule 37 180-day ageing), and remarks. Every ERP running in India will produce this extract natively — Tally, SAP, Oracle Fusion, Zoho Books, Busy, and Odoo all have a GST purchase register report that matches this shape. The purchase register versus GSTR-2B article documents the ERP export mechanics per system.
Input 2 — GSTR-2B extract from the GST portal
Log in to the GST portal, navigate to Returns Dashboard, select the return period, and download GSTR-2B in the JSON format the portal ships. Convert the JSON to Excel using the portal-provided offline utility or a spreadsheet-native JSON parser. Paste the B2B table and the B2B credit note table into the Input 2 sheet, retaining the columns for supplier GSTIN, supplier trade name, invoice number, invoice date, invoice type, taxable value, IGST, CGST, SGST, Cess, ITC availability status (Yes or No), and place of supply. The place-of-supply column matters because it is what the multi-GSTIN reconciliation uses to catch an ITC that was billed to one GSTIN of the group but the invoice was booked to another.
Input 3 — IMS action log
The GST portal provides a downloadable IMS action log per return period from the IMS dashboard. Download it in Excel form and paste it into the Input 3 sheet. The three columns the workbook needs are the supplier GSTIN, the invoice number, and the recorded action code — Accept, Reject, Pending, or Default-Accept. The IMS reconciliation article walks through how to read the dashboard and export it, and the IMS versus GSTR-2B article covers the interaction between IMS actions and the GSTR-2B publication timing that this workbook depends on.
Supplier master
A supplier master sheet — one row per unique GSTIN — carries the supplier name, contact email, contact phone, the standard TDS payment code applicable to the supplier’s category, and the supplier’s Rule 37A GSTR-3B filing status (Filed or Not Filed for the relevant period). The supplier follow-up list generates from this master.
The composite key and the match logic
The workbook’s driver sheet is a copy of the purchase register with additional computed columns. The match runs on a four-part composite key.
- Supplier GSTIN. Fifteen-character alphanumeric string, upper-cased and stripped of trailing whitespace via
=UPPER(TRIM(A2)). - Invoice number, normalised. GST invoice numbers commonly carry a financial-year prefix (
2526/), a series prefix (INV-), or a suffix (/GST,/R). The normalisation strips the noise usingTEXTAFTERandTEXTBEFORE. A formula like=TEXTAFTER(TEXTBEFORE(B2,"/GST"),"INV-",-1)on a value likeINV-2526/00417/GSTreturns2526/00417. Wrap the extraction in aLETbinding to keep the formula readable across the sheet. - Invoice date. Standardised to Excel serial date. Where the source is a text date,
=DATEVALUE()converts it; where the source uses add/mm/yyyystring with a two-digit year,=DATE(2000+MID(C2,7,2),MID(C2,4,2),LEFT(C2,2))handles the parse. - Taxable value within a tolerance. The amount match is a
ROUND(D2,2)comparison against the GSTR-2B taxable value with a rupee tolerance band captured in a configuration cell.=ABS(pr_amount - g2b_amount) <= tolerance_cellreturns TRUE for a within-tolerance match.
The driver row runs an XLOOKUP against the GSTR-2B sheet keyed on the concatenated composite key, and a separate XLOOKUP against the IMS action log keyed on GSTIN and normalised invoice number. Both lookups use XLOOKUP in exact-match mode with an #N/A-safe third argument returning an empty string, and both point at named ranges rather than volatile cell references so the sheet does not break when a supplier is added.
The double lookup produces three flags per row — is this in GSTR-2B, is this in IMS, and what is the IMS action code — and the categorisation formula reads all three to assign the row to one of the five buckets.
The five categorisation buckets
The driver sheet’s Bucket column is a nested IF (or SWITCH) formula that runs the following logic in order.
- Bucket 1 — Matched. The row is in the purchase register, in GSTR-2B on the composite key with amount within tolerance, and the IMS action is Accept (or Default-Accept if the invoice was correctly a Default-Accept for a legitimate supplier). This is the row that populates GSTR-3B Table 4. Colour-band it green in the driver.
- Bucket 2 — Supplier not filed. The row is in the purchase register but not in GSTR-2B for the tax period. The supplier has not yet filed GSTR-1. The invoice is legitimate; the ITC is at risk on the Section 16(4) November 30 clock. Colour-band it amber. This row will feed the at-risk queue.
- Bucket 3 — Ghost invoice. The row is in GSTR-2B but not in the purchase register. Either the buyer’s booking is missing (a real inbound invoice was not entered in the ERP) or the invoice is fraudulent (a supplier has claimed a supply that never happened). Colour-band it red. This row must be investigated within seventy-two hours and, if fraudulent, rejected on IMS before the cycle closes.
- Bucket 4 — IMS reject. The row’s IMS action is Reject. Investigate whether the reject was correct (the invoice was for a different entity, a wrong price, a fraudulent supplier claim, or a duplicate). If wrong, the invoice will need to be re-actioned in a subsequent IMS cycle — otherwise the ITC is lost on the Section 16(4) clock. Colour-band it orange.
- Bucket 5 — IMS pending. The row’s IMS action is still Pending at the time of the reconciliation. This must be resolved before the monthly IMS cycle closes on the fourteenth, otherwise the default Accept fires and the ITC flows to GSTR-2B in the next period. Colour-band it yellow.
The colour banding is a conditional-formatting rule tied to the Bucket column, not a manual highlight. A weekly refresh re-runs the categorisation and re-bands the sheet automatically.
The at-risk queue and Section 16(4) countdown
The at-risk queue is a computed sheet that filters the driver to Bucket 2 (Supplier not filed) and computes days-to-deadline against the Section 16(4) date.
A configuration cell holds the financial year being reconciled — for example, FY 2025-26. A helper formula computes the Section 16(4) target date for that year: =DATE(YEAR(config_fy_end)+1,11,30), which returns 30 November 2026 for FY 2025-26. The days-to-deadline column on each at-risk row is =target_16_4 - TODAY(), which recomputes on every open.
Four escalation bands read the days-to-deadline value:
- Over 90 days remaining — informational; visible in the queue but not surfaced in the weekly review.
- 61 to 90 days remaining — Tier 1 supplier follow-up letter goes out; standard template.
- 31 to 60 days remaining — Tier 2 supplier follow-up; the sales-side account owner is looped in via a second email.
- Under 30 days remaining — Tier 3 escalation to the controller; provisioning decision required.
A rupee-value summary at the top of the at-risk sheet totals the exposure per band, so the finance manager sees “₹12,84,000 under 30 days remaining” as the mandatory attention line before opening the underlying rows. The Rule 37 and 37A article covers the interaction between the Rule 37A September 30 supplier-filing clock and the Section 16(4) November 30 lockdown, both of which the at-risk queue is running against.
The supplier follow-up list
The supplier follow-up list is a second computed sheet. It runs UNIQUE(FILTER(gstin_column, bucket_column="Supplier not filed")) to produce one row per delinquent supplier, then runs COUNTIFS and SUMIFS to compute per-supplier metrics: count of unfiled invoices, total unfiled invoice value, at-risk ITC in rupees, earliest invoice date, days since that earliest invoice, and the minimum days-to-deadline across the supplier’s unfiled invoices. A final column left-joins the supplier master via XLOOKUP on GSTIN to pull the contact email and phone into a mail-merge-ready column.
The finance team then filters the follow-up sheet to the suppliers with under-90-days remaining, exports it to CSV, and mail-merges into the standard Rule 37A supplier-non-filing letter — a Word template that the wider reconciliation playbook blackbook sub-strand ships separately. The follow-up is a fortnightly rhythm on the twenty-day close cadence; anything more frequent is best served by continuously-refreshed reconciliation infrastructure.
The formulas at practitioner level
The formulas the workbook uses are entirely standard Excel — no add-in, no Power Query, no macro. The curation is in how they are wired together.
- GST invoice-number normalisation.
=TRIM(UPPER(TEXTAFTER(TEXTBEFORE(invoice_raw,"/GST"),"INV-",-1)))on the two most common Indian invoice-number patterns. Where the invoice number carries no known noise (a plain numeric string), anIFERRORwrapper returns the raw value unchanged. - Composite key concatenation.
=gstin & "|" & invoice_normalised & "|" & TEXT(invoice_date,"yyyymmdd"). The pipe delimiter is chosen because it does not appear inside any legitimate invoice number. - Composite-key XLOOKUP into GSTR-2B.
=XLOOKUP(pr_composite_key, g2b_composite_key_range, g2b_taxable_value_range, "", 0). Exact match,#N/A-safe return of empty string, no wildcards. - Tolerance amount match.
=IF(g2b_match_value="", FALSE, ABS(ROUND(pr_amount,2) - ROUND(g2b_match_value,2)) <= tolerance_cell). - IMS action lookup. A second
XLOOKUPkeyed on GSTIN and normalised invoice number, returning the action code column from the IMS input sheet. - Bucket assignment. A single
IFS()(Excel 2019+) or nestedIF()walks the three flags (in GSTR-2B, in IMS, IMS action code) and returns one of the five bucket labels. - Days-to-deadline countdown.
=IF(bucket="Supplier not filed", target_16_4 - TODAY(), ""). The blank return keeps the column tidy for non-at-risk rows.
Every formula uses LET where the intermediate values are referenced more than once, and every named range is defined via the Name Manager so the sheet is portable to a fresh workbook without cell-reference drift.
The companion interactive workbook
The interactive companion tool at /tools/three-way-itc-workbook/ runs the same logic in the browser. Paste a purchase register block, a GSTR-2B block, and an IMS action block, and the tool produces the bucketed driver, the at-risk queue against the Section 16(4) date, and the supplier follow-up list without a local Excel install. The tool is the recommended way to run the recipe if the finance team is evaluating the logic before committing to an internal workbook build, or if the reconciliation is being run from a laptop that does not have the current Excel version’s XLOOKUP and TEXTBEFORE functions.
The downloadable .xlsx version of this workbook — with named ranges, error traps, and a supplier master pre-populated with a common Indian vendor set — is on the Terra Insight downloadable assets catalogue and will publish in the Tier 1 release wave. Until then, the interactive companion runs the same recipe end to end.
When the manual workbook tops out
The three-way workbook is engineered for a finance team running its own reconciliation manually. It holds up cleanly below roughly 200 active vendors under GSTR-2B, roughly 3,000 monthly purchase invoices, and a single GSTIN. Above those thresholds — or on multi-GSTIN groups with more than three GSTINs, or in aggregator-heavy revenue models with more than four platforms — the at-risk queue needs daily refresh rather than weekly, and the Bucket 3 (Ghost invoice) and Bucket 4 (IMS reject) investigations consume the executive time the twenty-day close cadence was reserving for other windows. This is where the at-risk ITC queue becomes a first-class output of continuously-refreshed reconciliation infrastructure such as TransactIG, rather than a weekly Excel refresh — the point where the manual detection layer has topped out and the design layer’s High Action Priority controls need a continuous automated aging queue with escalation triggers.
Where this fits
- Reconciliation software India — pillar guide
- GST reconciliation software
- TDS reconciliation software
- The reconciliation playbook — monthly close pillar
Related reading
- GSTR-2B ITC reconciliation failure modes — what this workbook is designed to catch
- Section 16(4) ITC time bar
- Rule 37 and 37A ITC reversal — supplier default
- Invoice Management System reconciliation
- IMS versus GSTR-2B three-way reconciliation
- Section 17(5) blocked ITC
- DRC-01C ITC mismatch reply guide
Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
- ▸ Section 16(4), Central Goods and Services Tax Act 2017 — A registered person shall not be entitled to take Input Tax Credit in respect of any invoice or debit note for the supply of goods or services or both after the 30th day of November following the end of the financial year to which such invoice or debit note pertains, or furnishing of the relevant annual return, whichever is earlier. The Finance Act 2022 shifted this deadline from September 30 to November 30 and applied the extension retrospectively from 1 July 2017. There is no rectification, no condonation, and no recovery mechanism for ITC time-barred under this provision — which is why the at-risk queue in this workbook counts down against the November 30 date rather than against a rolling calendar.
- ▸ Rule 36(4), Central Goods and Services Tax Rules 2017 — Input tax credit availed by a registered person in respect of invoices or debit notes the details of which have not been furnished by the suppliers under Section 37 shall not exceed the amount of input tax credit available in respect of invoices or debit notes the details of which have been furnished by the suppliers under Section 37 in FORM GSTR-1 or IFF. The GSTR-2B ceiling is the hard cap that the three-way workbook computes against; nothing in the ITC-claimed column can exceed the ITC available in GSTR-2B for the same tax period once this rule is enforced.
- ▸ Rule 37, Central Goods and Services Tax Rules 2017 — A registered person who has availed of Input Tax Credit on any inward supply and who fails to pay the supplier the value of such supply along with the tax payable thereon within 180 days from the date of issue of invoice shall reverse the ITC availed, in the return furnished for the tax period immediately following the 180-day period. Re-availment is permitted on payment and is not subject to the Section 16(4) time bar. The 180-day clock is a second countdown that runs on the same purchase register the three-way workbook is built on.
- ▸ Rule 37A, Central Goods and Services Tax Rules 2017 (Notification 26/2022-Central Tax) — Where the ITC availed by a registered person in GSTR-3B pertains to an invoice for which the corresponding tax has not been paid by the supplier through GSTR-3B by the 30th day of September following the end of the financial year in which the ITC was availed, the recipient shall reverse the ITC in GSTR-3B on or before the 30th day of November following the end of that financial year. This is the supplier-side reversal trigger that keys against the supplier's GSTR-3B filing status, distinct from the Rule 36(4) GSTR-1 availment ceiling.
- ▸ Invoice Management System, GST portal advisory issued 2024 and rules amendments — The Invoice Management System (IMS) went live on the GST portal on 14 October 2024. Every inbound document filed by a supplier — invoice, credit note, or debit note — lands on the recipient's IMS dashboard in a Pending status. The recipient takes one of three actions — Accept, Reject, or Keep Pending — before the monthly IMS cycle closes. Only Accepted documents flow into the recipient's GSTR-2B. If the recipient takes no action by the cycle close, the default action is Accept. The IMS action log is the third input to the three-way match that this workbook operationalises.