VLOOKUP HSN Codes & GST Rates in Excel

Q: What is the VLOOKUP formula for GST rate?

`=VLOOKUP(code, HSNtable, rateColumn, FALSE)`. The `FALSE` forces an exact match so you get the right rate for that exact HSN code.

Q: Should I use VLOOKUP or XLOOKUP for HSN?

Use `XLOOKUP` if you have Excel 2019 or 365 — it avoids column-counting and has a built-in not-found message. `VLOOKUP` is fine on older versions.

Q: How do I find a code I don't know yet?

Start with our [HSN & SAC Code Finder for common trades](https://ankeshan.com/gst/hsn-code-finder/), then add it to your master. ---

Last updated: 27 June 2026 · Lookup formulas for the GST 2.0 rates (Nil / 5% / 18% / 40%).

To pull an HSN code's GST rate into your invoice or register, keep a master sheet of codes, descriptions and rates, then use VLOOKUP(code, HSNtable, column, FALSE) — or the cleaner XLOOKUP(code, codes, rates). This fills the description and the correct rate automatically, so you never type a stale rate by hand and your GSTR-1 Table 12 stays consistent. Always use exact match (FALSE) for HSN lookups.

Key takeaways

Build an HSN master once: columns for code, description and current GST rate.
VLOOKUP(value, table, col, FALSE) returns the matching value; the final FALSE forces an exact match — essential for codes.
XLOOKUP is better here: no column-counting, can look left, and returns a clean message if not found.
Store codes as text, not numbers, so leading zeros and 6–8 digit codes don't get mangled.
Wrap in IFERROR to show "Check HSN" instead of #N/A when a code is missing.
A single master means a rate change is one edit, not a hunt through every invoice.

Fact box. HSN (Harmonized System of Nomenclature) codes classify goods; SAC codes classify services. Businesses up to ₹5 crore turnover report 4-digit HSN, and those above ₹5 crore report 6-digit HSN on invoices. (Source: GSTN HSN reporting advisory, Table 12.)

How do I set up an HSN master sheet?

Create a sheet named HSN with at least three columns:

A — HSN/SAC code	B — Description	C — GST rate (%)
0401	Milk	0
3401	Toilet soap	5
8528	Television	18
2402	Cigarettes	40

Format column A as Text before typing codes, so 0401 keeps its leading zero. Keep one row per code and never duplicate a code — VLOOKUP returns only the first match.

How does VLOOKUP work for HSN codes?

VLOOKUP searches the first column of a table and returns a value from a column to its right. With the code in B8:

Description: =VLOOKUP(B8, HSN!$A:$C, 2, FALSE)
GST rate: =VLOOKUP(B8, HSN!$A:$C, 3, FALSE)

The arguments are: lookup value, table range, column number to return, and FALSE for exact match. Lock the table with $ so it doesn't shift when you copy the formula down.

Always use FALSE. Without it, VLOOKUP does an approximate match and can return the wrong rate for a similar code. For HSN, exact match is non-negotiable.

Why is XLOOKUP better for this?

XLOOKUP (Excel 2019/365) avoids the two biggest VLOOKUP traps — counting columns and the lookup column having to be first.

Rate: =XLOOKUP(B8, HSN!A:A, HSN!C:C, "Check HSN")

The fourth argument is what to show when nothing matches, so you don't need a separate IFERROR. It also looks up regardless of column order, so your master can put description before code if you like.

Fact box. XLOOKUP searches a lookup array and returns from a separate return array, with a built-in "if not found" argument — removing the column-index and left-lookup limitations of VLOOKUP. (Source: Microsoft Excel function reference.)

How do I stop #N/A errors?

A missing or mistyped code returns #N/A. Trap it so the sheet stays readable:

=IFERROR(VLOOKUP(B8, HSN!$A:$C, 3, FALSE), "Check HSN")
With XLOOKUP, use the built-in argument: =XLOOKUP(B8, HSN!A:A, HSN!C:C, "Check HSN")

Seeing "Check HSN" tells you to add the code to your master rather than silently billing a blank rate.

Common pitfalls

Problem	Cause	Fix
Leading zero dropped (0401 → 401)	Code stored as number	Format column as Text
Wrong rate returned	Approximate match (`TRUE`/omitted)	Use `FALSE` for exact match
`#N/A` on a valid code	Trailing spaces or different format	`TRIM()` the code; keep formats identical
Only first match returned	Duplicate codes in master	Keep one row per code

How Ankeshan helps: Ankeshan ships an HSN/rate master that updates from our servers, so when a slab changes your invoices pick up the new rate — no VLOOKUP to re-point. It's launching soon; join the waitlist.

Frequently asked questions

What is the VLOOKUP formula for GST rate? =VLOOKUP(code, HSNtable, rateColumn, FALSE). The FALSE forces an exact match so you get the right rate for that exact HSN code.

Should I use VLOOKUP or XLOOKUP for HSN? Use XLOOKUP if you have Excel 2019 or 365 — it avoids column-counting and has a built-in not-found message. VLOOKUP is fine on older versions.

Why does my HSN code lose its leading zero? Excel treats it as a number. Format the cell as Text before entering the code, or store codes with a leading apostrophe.

How many HSN digits do I need? Four digits for turnover up to ₹5 crore (B2B), six digits above ₹5 crore. Keep your master at the digit length you must report.

How do I find a code I don't know yet? Start with our HSN & SAC Code Finder for common trades, then add it to your master.

Sources

GSTN HSN reporting advisory (Table 12, GSTR-1) — gst.gov.in.
Microsoft Excel function reference (VLOOKUP, XLOOKUP).

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.