Most accountants start with Excel, and most of them are right to. The question is where it stops paying for itself. This post takes one made-up month of 300 purchase invoices, shows what a books-to-GSTR-2B lookup reports and what is actually true, and then lists what you would have to build by hand to close the gap. If a tool still looks unnecessary at the end, the section on when manual is fine is for you.
Prefer a spreadsheet? Our GSTR-2B reconciliation in Excel guide includes a free template.
What a 300-invoice month looks like to a VLOOKUP
The figures below are invented to show a pattern, not taken from a survey. A trading firm has 300 rows in its purchase register. Someone runs an exact-match lookup of GSTIN plus invoice number from the register into the GSTR-2B B2B sheet. The lookup says 241 rows found and 59 not found (#N/A). Here is the truth behind those two numbers.
| What the row really is | Rows | What the lookup shows | Real position |
|---|---|---|---|
| In GSTR-2B, tax agrees | 233 | Found | Fine |
| In GSTR-2B, tax differs by more than ₹1 | 5 | Found | Wrong tax, unless you added a value check |
| Same invoice booked twice | 3 | Found, both rows | Register overstates ITC by ₹27,000 |
| In GSTR-2B, number written differently | 31 | #N/A | False alarm |
| Supplier has not filed anything for you | 6 | #N/A | ITC blocked |
| Supplier filed other invoices, not this one | 14 | #N/A | IMS Pending, omission or keying error |
| Reported by the supplier in a later period | 8 | #N/A | Timing |
| Total register rows | 300 |
Read the table twice. The lookup raised 59 alarms and 31 of them are false, so the accountant spends the first hours on invoices that were never a problem. And it stayed quiet about the two things that matter most: the three duplicate rows found a partner and looked healthy (₹7,200 + ₹15,300 + ₹4,500 = ₹27,000 of ITC counted twice in the register), and the 11 invoices worth ₹49,500 of tax that sit in GSTR-2B but were never booked do not appear at all, because a books-to-2B lookup only walks through your rows. The manual method is noisy where it is cheap and silent where it is expensive.
Side by side for the same month
| Question | Manual (VLOOKUP or XLOOKUP) | Reconciliation tool |
|---|---|---|
| Invoice-number clean-up | You build helper columns and keep them consistent every month | Fixed clean-up applied to both files: capitals, punctuation removed, leading zeros dropped |
| Invoices only in GSTR-2B | Needs a second lookup in the opposite direction | Listed as their own bucket |
| Duplicate rows in books | Needs a COUNTIF column you must remember to add | Second row goes to a Duplicates list and is counted once |
| Tax comparison | A separate formula with your own tolerance | Tax within ₹1 is matched, more than ₹1 is a value mismatch |
| Credit and debit notes | Sit on another sheet; easy to leave out | Listed for information; full two-way matching is not built |
| Supplier absent vs invoice absent | Both show #N/A; you separate them by eye | Separated into two buckets by rule |
| Audit trail | Formulas visible, but drift when someone sorts one column | CSV export of the bucketed result |
| Time | Depends on how messy the numbering is; the clean-up is the long part | The matching step is instant; the time goes into reading exceptions |
On time, we will not quote a stopwatch figure, because it depends entirely on your files. What we can say from our own QA run is that the matching step for a 1,190-invoice test file took 488 milliseconds in a test browser on one machine. The time in either method is spent on exceptions, and the tool’s advantage is that the noisy alarms and the silent misses are handled by rule before you start reading.
What manual matching misses, and why
The GSTR-2B Excel has more than one sheet
The Excel downloaded from the portal contains B2B, B2BA, CDNR and CDNRA tables. A lookup against the B2B sheet alone ignores amendments and credit or debit notes. Our guide to the GSTR-2B Excel file and its tabs shows what each sheet holds.
Text and numbers do not compare
An invoice number typed as 000542 in one file and stored as the number 542 in the other is a different value to Excel. A trailing space in a GSTIN does the same. Invoice number mismatches explains the usual patterns.
A tax invoice number has limits, and your data may not respect them
Under Rule 46(b) an invoice number is a serial of at most sixteen characters using letters, numerals, hyphen and slash. So a space or a hash in your number column is a keying artefact, and it is the first thing to strip in a helper column.
Sorting one column breaks every formula that points at it
This one has no rule behind it, only experience. A filter applied to one column of a lookup sheet, or a paste that shifts a range, can silently point every formula at the wrong row. Nothing turns red. Freeze your source sheets and paste values into a separate working sheet.
If you stay manual, the minimum that works
Do not skip these five, because each one closes a row of the table above.
- One key column on each side made from the GSTIN and a cleaned invoice number, for example =UPPER(TRIM(A2))&"|"&UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"/",""),"-","")," ","")), with the invoice column formatted as text before you paste.
- A duplicate flag on the register: =COUNTIF($E$2:$E$301,E2)>1. Take the second and later rows out of the ITC total.
- Lookups in both directions: register to GSTR-2B, and GSTR-2B to register, using ISNUMBER(MATCH(key,range,0)).
- A tax difference column for found rows, with a tolerance of ₹1 so that rounding does not bury real exceptions.
- A summary check: register tax minus GSTR-2B tax must equal the sum of your exception rows. If it does not, something is unclassified.
Our guide on reconciling GSTR-2B in Excel without Tally goes through the spreadsheet route in more detail.
When manual is the right answer
- Small volume, clean numbering. A single supplier list of a few dozen invoices from one ERP export, where the numbers are copied straight from the same software.
- You only need a yes or no. If the question is just “is invoice X in this month’s GSTR-2B?”, a filter on the sheet answers it.
- You are teaching a junior. Building the key columns once by hand teaches more than any report does.
- You need to explain every cell. Some engagements need a formula trail a reviewer can click through.
Also be clear about what a tool does not do. Ours does not file returns, does not connect to the GST portal, does not read your live IMS dashboard and does not reconcile GSTR-2A. It compares two files you give it.
Why the calendar matters: GSTR-2B is generated on the 14th and, for monthly filers, GSTR-3B is due on the 20th. That leaves six days. Every hour spent cleaning invoice numbers on the 14th is an hour not spent chasing the suppliers who actually blocked your credit. The 14th to 20th timeline shows a day-by-day plan.
A practical split
Many firms end up with a hybrid, and it works well. Run the first pass in a tool so that clean-up, duplicates and both directions are done by rule. Export the CSV. Then do the human work in Excel: sort the exceptions by rupee value, pair up the invoice that appears once as missing and once as extra, and write the supplier follow-ups. If your register includes credit notes, keep them in your own sheet for now, since matching of credit notes against your books is not something the tool does yet.
Frequently asked questions
Can XLOOKUP do what VLOOKUP cannot in GSTR-2B matching?
XLOOKUP is easier to write and can look to the left, but it still compares exact text. It does not clean invoice numbers, does not search in the reverse direction and does not flag duplicates. The helper columns matter more than the lookup function.
How many invoices a month before I should stop using Excel?
There is no legal threshold. As a working guide, if you spend more time cleaning invoice numbers and chasing false alarms than reading real exceptions, or you handle more than one GSTIN, move the first pass to a tool and keep Excel for the exception list.
Do I still need Excel if I use a reconciliation tool?
Yes, for the follow-up. The tool exports its report as a CSV, and the supplier chasing, corrections to your books and the ITC working for GSTR-3B still happen in your own sheets and accounting software.
Try the first pass on your own month
Load your GSTR-2B and purchase register, confirm the column mapping, and get every invoice sorted into matched, value mismatch, missing, IMS Pending or only in GSTR-2B, with rupee totals. Your GSTR-2B and purchase register files are read in your browser and never uploaded to any server.
Related guides
- GSTR-2B reconciliation in Excel: a free template and how to use it
- 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
- How to use a GSTR-2B reconciliation tool
- GSTR-2B matching in SAP S/4HANA: ingestion, BAPI reconciliation, and GR/IR clearing
- GST portal offline matching tool or a browser tool: which fits your GSTR-2B check
- GSTR-2B JSON to Excel conversion guide
Last checked: 30 Sep 2026. Sources: GSTN FAQs on comparison of liability declared and ITC claimed, GSTN user guide on viewing e-filed returns, GSTN GSTR-3B user guide, CGST Rules, Rule 46.