Finance & Accounting
Sales Tax Rate Lookup with Tax-Exempt Override (XLOOKUP + IF)
Calculate invoice sales tax and gross total dynamically by combining IF(Tax_Exempt = "Yes", 0, XLOOKUP(...)) with missing-jurisdiction validation.
Combining a resale/B2B tax-exempt flag with jurisdiction rate lookup without #N/A invoice crashes!
B2B SaaS and wholesale invoices need two linked rules before multiplying Net_Subtotal by a state or VAT rate: first, if the customer holds a verified resale or VAT reverse-charge exemption (Tax_Exempt = "Yes"), tax must evaluate to $0.00 regardless of state; second, if an order ships to an unconfigured state code, the formula must flag "Unconfigured Tax Jurisdiction" instead of silently charging 0% or crashing the ledger with #N/A!
Testing XLOOKUP(F3, A2:A5, B2:B5, -1) catches unconfigured jurisdictions cleanly (< 0), while IF(F4 = "Yes", 0, ROUND(F2 * Rate, 2)) computes penny-rounded tax ($1,250.00 * 8.25% TX = $103.13, gross total $1,353.13; $0.00 when Exempt = "Yes")!
| 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(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(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.