What are the Best GST Reconciliation Formulas in Excel to Use With My ERP Exports?

The most useful Excel formulas for GST reconciliation are XLOOKUP, IFERROR, IF, ABS, SUMIFS, COUNTIFS and TEXT. These functions help businesses compare ERP exports with GSTR-2B, GSTR-1 and other GST return data, making it easier to match invoices, identify missing input tax credit (ITC) entries, detect GST variances and analyse large datasets without manually reviewing thousands of transactions.

Which Excel Formulas Are Most Useful for GST Reconciliation?

Businesses commonly use XLOOKUP, IFERROR, IF, ABS, SUMIFS, COUNTIFS and TEXT to reconcile ERP exports with GSTR-2B and other GST data, reducing manual effort and helping identify exceptions quickly.

Prepare your data first

Before using GST reconciliation formulas, ensure both datasets use the same structure. For example, when reconciling a purchase register against GSTR-2B, both files should ideally contain the following fields:

• GSTIN
• Invoice Number
• Invoice Date
• Taxable Value
• GST Amount

Additional fields, such as Place of Supply, Tax Rate or Invoice Type, can also be included when more detailed reconciliation is required.

Create a reconciliation key

One of the biggest causes of mismatches is inconsistent invoice formatting. For example, the same GSTIN + invoice number combination may appear differently in different files due to typos or naming errors:

• GSTIN: 27ABCDE1234F1Z5
• Invoice Number: INV-1001

may appear as:

• GSTIN: 27ABCDE1234F1Z5
• Invoice Number: INV1001

Instead of matching only on invoice numbers, create a reconciliation key.

Excel Formula

=A2&SUBSTITUTE(B2,"-","")

This formula combines multiple fields into a single unique identifier and removes formatting differences that could prevent invoices from matching correctly.

Assuming:

• A2 = GSTIN
• B2 = Invoice Number

The output will be: 27ABCDE1234F1Z5INV1001

Create the same key in both datasets. This helps prevent mismatches caused solely by invoice formatting variations.

Use XLOOKUP for invoice matching

XLOOKUP searches for a value in one dataset and returns the corresponding result from another dataset. In GST reconciliation, it is commonly used to check whether an invoice exported from an ERP appears in GSTR-2B or another GST report.

Excel Formula

=XLOOKUP(E2,$J$2:$J$5000,$J$2:$J$5000,"Missing")

Where:

• E2 = ERP reconciliation key
• J column = GSTR-2B reconciliation keys

For example, assume your purchase register and GSTR-2B entries look like this:

Purchase Register

• INV1001
• INV1002
• INV1003

GSTR-2B

• INV1001
• INV1003

The output will be: 

The formula can instantly identify invoices recorded in books but not appearing in GSTR-2B. This is particularly useful because ITC claims are tied to invoices reflected in GSTR-2B.

Use IFERROR for cleaner reconciliation reports

IFERROR replaces Excel error messages with a custom result. During reconciliation, it is often used with XLOOKUP to display "Missing" instead of showing #N/A when an invoice cannot be found.

Excel Formula

=IFERROR(XLOOKUP(E2,$J$2:$J$5000,$J$2:$J$5000),"Missing")

This makes reconciliation reports easier to review by replacing technical errors with actionable statuses.

Use IF to create reconciliation statuses

IF evaluates a condition and returns one result when the condition is true and another when it is false. It is useful for automatically assigning statuses such as Matched, Missing or Mismatch.

Excel Formula

=IF(F2="Missing","Vendor Follow-up","Matched")

Example:

This allows invoices to be categorised automatically based on reconciliation results.

Use ABS to identify GST differences

Sometimes invoices appear in both datasets, but the GST amounts differ. ABS returns the absolute value of a number by removing its sign. In GST reconciliation, it helps calculate the size of a tax difference without worrying about whether the variance is positive or negative.

Excel Formula

=ABS(A2-B2)

It can also be used with the COUNT function to count the number of files or entries with a variance. For example, if 500 invoices are reconciled and only 10 show GST differences, ABS helps quantify those variances so that finance teams can sort or filter variances to focus on the largest discrepancies first.

Use SUMIFS for supplier-wise GST reconciliation

Businesses often need to compare supplier totals rather than individual invoices. SUMIFS adds numbers that meet one or more specified conditions. Businesses often use it to calculate supplier-wise GST totals, taxable values or ITC amounts during reconciliation.

Excel Formula

=SUMIFS(B:B,A:A,"ABC Ltd")

Suppose your purchase register looks like this:

The output will be: 70,000 (45,000 + 25,00)

Use COUNTIFS to quantify mismatches

COUNTIFS counts records that meet one or more conditions. It is useful for measuring the number of missing invoices, mismatches or high-value exceptions identified during reconciliation. 

Excel Formula

=COUNTIFS(F:F,"Missing")

You can also use variations such as: =COUNTIFS(F:F,"Missing", G:G," >50000")

This counts only missing invoices above ₹50,000, so your finance team can prioritise high-value discrepancies first.

Use TEXT to standardise invoice dates

Date formatting differences often create unnecessary mismatches. TEXT converts dates, numbers or values into a consistent format. During GST reconciliation, it is commonly used to standardise invoice dates before comparing data from different sources.

Excel Formula

=TEXT(A2,"dd-mm-yyyy")

For example, this formula can convert different inputs like:

• 01/06/2026
• 1-Jun-26

The standard output will be: 01-06-2026

Conclusion

Excel formulas such as XLOOKUP, IFERROR, SUMIFS and COUNTIFS can streamline GST reconciliation by automating invoice matching, identifying missing input tax credit (ITC) entries and highlighting GST variances. However, spreadsheets are most effective for supplementary analysis rather than routine compliance. 

For ongoing GST reconciliation, TallyPrime provides built-in GSTR-2A and GSTR-2B reconciliation, automated matching and exception identification, helping businesses reduce manual effort, improve accuracy and manage GST compliance more efficiently.