Excel formula: Basic Tax Rate calculation with VLOOKUP

Generic formula

=VLOOKUP(amount,tax_table,2,TRUE)

Summary

To calculate a tax rate based on a simple tax rate table, you can use the VLOOKUP function. In the example shown, the formula in G5 is:

=VLOOKUP(G4,tax_table,2,TRUE)

where "tax_table" is the named range C5:D8.

Note: this formula determines a single tax rate. To calculate tax based in a progressive system where income is taxed across multiple brackets at different rates, see this example.

Explanation

VLOOKUP requires lookup values to be in the first column of the lookup table. Because the first column in the example is actually "Band", we are purposely excluding this column from the table range provided to VLOOKUP, which is configured like this:

Lookup value itself comes from G4
Table array is the named range tax_table (C5:D8)
Column index number is 2, since tax rates are in the second column of C5:D8
Range_lookup argument is set to TRUE = approximate match

With this configuration, VLOOKUP scans the lookup values until it finds a value higher than the value in G4, then VLOOKUP "drops back" to the previous row and returns the tax rate in that row. Because we are using using VLOOKUP in approximate match mode, lookup values must be sorted in ascending order.

VLOOKUP matching modes

VLOOKUP has two matching modes: exact match and approximate match, controlled by an optional forth argument called "range_lookup". The default behavior is approximate match. In this example the forth argument has been set to TRUE explicitly for clarity. More detailed information on VLOOKUP here.

Author

Dave Bruns

Related formulas

Income tax bracket calculation

In G5, the first VLOOKUP is configured to retrieve the cumulative tax at the marginal rate with these inputs: Lookup value is "inc" (G4) Lookup table is "rates" (B5:D11) Column number is 3, Cumulative tax Match type is 1 = approximate match VLOOKUP...

Calculate shipping cost with VLOOKUP

The core of the formula is VLOOKUP, which is configured in approximate match mode by setting the forth argument to 1 or TRUE. In approximate match mode, the values in the first column of VLOOKUP must be sorted. VLOOKUP will return a value at the...

Calculate grades with VLOOKUP

In this example, the scores in column F are lookup values for the VLOOKUP function. The lookup table is the named range "key" (B5:C9). The column index is provided as 2, since we want VLOOKUP to return a grade from the second column. Finally, the...

Tax rate calculation with fixed base

The core of this formula is a single IF statement that checks the amount in column B against the base limit: = IF ( B5 < limit If TRUE, the formula simply multiplies the amount in B5 by tax rate: B5 * rate If FALSE, the formula applies the tax...

Related functions

Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.