Arte SheetsLearn. Try. Understand.

Finance & Accounting

Illustrative Tax-Rate Lookup with an Exemption Flag

Practice a spreadsheet rate lookup and a pre-set exemption flag using fictional jurisdictions and rates.

A fictional rate-table exercise

This is a mathematical exercise with fictional jurisdiction codes and sample rates. It does not determine where tax is due, whether a customer qualifies for an exemption, whether reverse charge applies, or which rate is legally correct. Real sales tax and VAT depend on the product, customer, location, registration and applicable rules; use authoritative current guidance and qualified tax advice for actual invoices.

In the example a pre-verified Yes flag returns zero, No looks up the configured sample rate, and a missing code displays an error. At an illustrative 8.25% rate on 1,250, the tax is 103.13 after rounding to cents. Validate the flag and the source data before applying any real billing rules.

Order Scenario (Subtotal F2 = $1,250)Jurisdiction F3 & Exempt F4Matched RateSales Tax & Gross Invoice Total
Taxable Texas OrderTX / Exempt = "No"8.25% (0.0825)Tax = $103.13 / Gross = $1,353.13
Tax-Exempt Texas ResellerTX / Exempt = "Yes"0.00% OverrideTax = $0.00 / Gross = $1,250.00
Unconfigured State "WA"WA / Exempt = "No"Missing (-1 Sentinel)"Unconfigured Tax Jurisdiction"

Conditional Sales Tax Amount with Tax-Exempt Override & Jurisdiction Guard

Returns 0 when Exempt flag F4 = "Yes", flags missing state codes in A2:A5, and otherwise rounds F2 * rate to 2 decimal places.

=IF(AND(F4 <> "Yes", F4 <> "No"), "Invalid Exempt Flag", IF(F4 = "Yes", 0, IF(XLOOKUP(F3, A2:A5, B2:B5, -1) < 0, "Unconfigured Tax Jurisdiction", ROUND(F2 * XLOOKUP(F3, A2:A5, B2:B5, -1), 2))))

Gross Tax-Inclusive Invoice Total ($)

Adds penny-rounded sales tax to Net Subtotal F2.

=IF(AND(F4 <> "Yes", F4 <> "No"), "Invalid Exempt Flag", IF(F4 = "Yes", ROUND(F2, 2), IF(XLOOKUP(F3, A2:A5, B2:B5, -1) < 0, "Unconfigured Tax Jurisdiction", ROUND(F2 + ROUND(F2 * XLOOKUP(F3, A2:A5, B2:B5, -1), 2), 2))))

Try the example

Use the original values or change them to test your understanding.

Further reading