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 F4 | Matched Rate | Sales Tax & Gross Invoice Total |
|---|---|---|---|
| Taxable Texas Order | TX / Exempt = "No" | 8.25% (0.0825) | Tax = $103.13 / Gross = $1,353.13 |
| Tax-Exempt Texas Reseller | TX / Exempt = "Yes" | 0.00% Override | Tax = $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.