How to Reconcile GSTR-2B in Excel Without Tally or Complex VLOOKUPs

Every month on the 14th, thousands of Indian accounts teams and business owners log into the GST portal, download their official GSTR-2B spreadsheet, and face the exact same frustrating barrier: how do I match this against my purchase register without spending hours writing fragile Excel formulas or buying an overpriced ERP add-on?

Prefer a spreadsheet? Our GSTR-2B reconciliation in Excel guide includes a free template.

If your business uses spreadsheets, Busy, Zoho Books, Marg, QuickBooks, or a custom billing system rather than TallyPrime, or if your CA handles Tally while you manage everyday operations in Excel, reconciliation often feels like an uphill battle.

Most advice online pushes two flawed extremes:

1. The manual formula route: Writing complex VLOOKUP, INDEX(MATCH), or XLOOKUP formulas with string cleanup helpers across two sheets with mismatched column headers.
2. The macro route: Downloading unverified .xlsm macro workbooks from tax forums that throw Windows Defender warnings, lock up on large files, and demand strict, hardcoded column layouts.

In this guide, we break down why traditional Excel reconciliation breaks, compare the 4 most common approaches head-to-head, and demonstrate how you can reconcile your GSTR-2B Excel directly against your books in under 60 seconds with zero template reformatting.

The Reality of GSTR-2B in Excel: Why Manual VLOOKUP Breaks

On paper, matching two spreadsheets should take two minutes. In practice, simple VLOOKUP matching fails on 15% to 30% of records. Here is why:

1. Inconsistent Unique Keys

A tax invoice is not uniquely identified by invoice number alone; it requires a compound key of Supplier GSTIN + Invoice Number. If you attempt a single VLOOKUP(Invoice_No, ...), different vendors using common series like INV-001 or 001 will collide. If you concatenate GSTIN and Invoice Number, any trivial formatting difference breaks the lookup.

2. Incompatible Numbering Syntax

Vendors rarely write invoice numbers in their GSTR-1 filings exactly as your accounts executive entered them:

• Your books record: INV/2026/042 while GSTR-2B reports: INV-2026-042 or 42
• Your books record: 000542 (stored as text), and Excel strips leading zeros, converting it to numeric 542

A standard exact-match VLOOKUP treats these as completely different records, falsely blocking your eligible ITC. See our dedicated guide on handling invoice number mismatches and fuzzy matching.

3. Header and Sheet Variations

The GST portal exports GSTR-2B as a multi-sheet workbook (B2B, B2BA, CDNR, ISD) with nested headers, merged cells, and text-formatted numbers. Any copy-paste or trailing whitespace in GSTIN fields instantly causes #N/A errors.

4 Approaches to GSTR-2B Reconciliation Compared

Approach 1: The Manual Formula Route

Building a functional manual reconciliation workbook requires helper columns for GSTIN + normalized invoice number, numeric format conversions, rounding tolerances, and reverse lookups. Verdict: Feasible for 20 invoices a month. For 100 to 5,000 invoices, it consumes entire working days.

Approach 2: Macro Sheets (.xlsm)

While they automate formula calculation, modern Windows and Office 365 security policies block macros downloaded from the internet. Most macros also expect exact column sequences and silently fail on newer Invoice Management System (IMS) states or credit note reversals.

Approach 3: Generic Cloud Portals & SaaS Tools

These introduce friction through forced registration, JSON dependency (wait 20 minutes for a JSON download instead of the instant .xlsx export), and data privacy concerns: uploading your complete purchase register hands over sensitive business intelligence (vendor pricing, payment terms) to third-party cloud servers.

Approach 4: Client-Side In-Browser Reconciliation

With gstr2bmatch.in, your browser's local JavaScript engine does the heavy lifting. You drag and drop both raw files. No data leaves your computer. Columns are auto-detected, compound keys are normalized, and records are categorized into actionable buckets in under 3 seconds.

Step-by-Step: Reconciling Native GSTR-2B Excel in Under 60 Seconds

Step 1: Download GSTR-2B from the GST Portal

Log into gst.gov.in → Return Dashboard → Select Financial Year and Month → Under Auto-drafted ITC Statement GSTR-2B, click Download Excel. Do not open, reformat, or rename the columns.

Step 2: Export Your Purchase Register

Export your monthly purchase register from your accounting software with: Supplier GSTIN, Invoice Number, Invoice Date, Taxable Value, IGST, CGST, and SGST amounts. Whether you use our Zoho Books guide, Busy ERP, or a custom CSV, leave the column names as they are.

Step 3: Reconcile on gstr2bmatch.in

1. Open gstr2bmatch.in
2. Drag and drop your GSTR-2B Excel file into the designated drop zone
3. Drag and drop your Purchase Register Excel/CSV into the second drop zone
4. The smart column mapper automatically recognizes standard variations. Confirm mappings if needed.
5. Click Run Reconciliation

Understanding Your 5 Reconciliation Buckets

• Bucket A: GSTIN, invoice number, and tax amounts match within tolerance (±₹1). Ready for instant ITC claim in GSTR-3B Table 4(A)(5). (Notes added if date differs by 5+ days or ITC is partial/restricted).
• Bucket B: GSTIN and invoice number match, but tax amounts differ by more than ±₹1. Requires checking for rate errors or amended invoices.
• Bucket C: Present in your books but missing in GSTR-2B, and the supplier has no other invoices in your 2B. Supplier likely missed the filing deadline.
• Bucket D: Present in GSTR-2B but missing in your books. Unclaimed credit or unrecorded bills requiring investigation.
• Bucket E: Present in your books but missing in 2B, however the supplier does appear in your 2B for other bills. These are likely stuck in the Invoice Management System (IMS) as Pending.

Essential Best Practices for Monthly Excel Reconciliation

1. Reconcile on the 14th or 15th: GSTR-2B is static and generated on the 14th. Reconciling immediately leaves 5 full working days before the 20th GSTR-3B deadline.
2. Watch for IMS Statuses: Invoices marked “Pending” in the Invoice Management System are excluded from GSTR-2B. See our guide on IMS Pending invoices and blocked ITC.
3. Keep an Audit Trail: Always export your reconciled matching report and store it in your monthly tax filing folder for potential notices 18 months later.
4. Enforce Vendor Discipline: If a vendor consistently appears in Bucket D, institute a policy requiring proof of GSTR-1 filing before releasing the tax portion of their payment.

Related guides

• GSTR-2B reconciliation in Excel: a free template and how to use it
• GSTR-2B VLOOKUP: whether Excel VLOOKUP is enough or you need a tool
• Tally purchase register for GSTR-2B in Excel: exporting the register to Excel without broken headers
• What kinds of GSTR-2B reconciliation software exist, and which fits me
• GSTR-2B JSON to Excel conversion guide
• How to use a GSTR-2B reconciliation tool
• How to reconcile GSTR-2B with your accounting software
• How to do GSTR-2B reconciliation in Tally Prime, step by step

To check your own purchase register against GSTR-2B, use the free GSTR-2B reconciliation tool. Your files are read in your browser and never uploaded to any server.