Look Up Sales Tax by Region

Excel Formulas › Business

All versionsVLOOKUP

Different states or regions, different tax rates. Keep the rates in a small table and let VLOOKUP (or XLOOKUP) pull the right one by region code — so updating a rate never means editing a formula.

Quick formula: with a rate table in E2:F6 (region, rate) and the order’s region in B2:
=amount * VLOOKUP(B2, $E$2:$F$6, 2, FALSE)
FALSE forces an exact region match. Multiply the matched rate by the order amount for the tax.

Functions used (tap for the full reference guide):

VLOOKUPXLOOKUP

The example

A Texas order taxed at the TX rate from the table.

ABC
1RegionRate
2CA7.25%
3TX6.25%
4NY4.00%
5$200 order in TX → tax$12.50

The formula

Match the region, apply the rate:

=B2 * VLOOKUP(C2, $E$2:$F$6, 2, FALSE) // $200 in TX → 6.25% → $12.50

How it works

An exact-match lookup keeps rates out of your formulas:

  1. Build a rate table: region code in the left column, tax rate beside it. Order doesn’t matter for exact match.
  2. VLOOKUP(region, table, 2, FALSE) returns the matching rate. FALSE means exact match — essential for codes.
  3. Multiply by the order amount for the tax. When a rate changes, edit the table; every formula updates.
  4. In Excel 365, XLOOKUP(region, regions, rates) does the same and lets you add an “if not found” message.

Catch unknown regions: a missing code returns #N/A with VLOOKUP. Wrap it: =IFERROR(VLOOKUP(…), 0) to default to zero tax, or use XLOOKUP(…, "rate?") to flag it for review rather than silently charging nothing.

Try it: interactive demo

Live demo

Pick a region and amount.

Rate · Tax

Variations

XLOOKUP (365)

With a not-found message:

=B2 * XLOOKUP(C2, regions, rates, "no rate")

Default to zero

Unknown region → 0 tax:

=B2 * IFERROR(VLOOKUP(C2, $E$2:$F$6, 2, FALSE), 0)

Tax-inclusive back-out

Extract tax from a gross price:

=gross - gross / (1 + rate)

Pitfalls & errors

Use FALSE for codes. Region codes need exact match. Approximate match (TRUE) on unsorted text returns garbage.

Handle #N/A. An unknown region errors. Wrap with IFERROR or XLOOKUP’s not-found argument so orders don’t break.

Trailing spaces in codes. “TX ” won’t match “TX”. TRIM the lookup value if data entry is messy.

Practice workbook

📊
Download the free Look Up Sales Tax by Region practice workbook
A tax-by-region sheet with VLOOKUP, the XLOOKUP, default-zero, and back-out variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up sales tax by region in Excel?
Keep a region/rate table and use =amount * VLOOKUP(region, table, 2, FALSE). FALSE forces an exact region match.
How do I handle an unknown region?
Wrap it: =amount * IFERROR(VLOOKUP(...),0) to default to zero, or use XLOOKUP with a not-found message to flag it.
How do I extract tax from a tax-inclusive price?
Back it out: =gross - gross/(1+rate) gives the tax portion of a gross amount.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Invoice total with tax · VLOOKUP basics · Tax bracket lookup

Function references: VLOOKUP · XLOOKUP