Exceljet

Quick, clean, and to the point

Basic Tax Rate calculation with VLOOKUP

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 fourth argument called "range_lookup". The default behavior is approximate match. In this example the fourth argument has been set to TRUE explicitly for clarity. More detailed information on VLOOKUP here.

Author 
Dave Bruns
Related formulas 
Excel formula: Income tax bracket calculation
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...
Excel formula: Calculate shipping cost with VLOOKUP
Calculate shipping cost with VLOOKUP
The core of the formula is VLOOKUP, which is configured in approximate match mode by setting the fourth 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...
Excel formula: Calculate grades with VLOOKUP
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...
Excel formula: Tax rate calculation with fixed base
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
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...

Related videos 
How to use VLOOKUP
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes. VLOOKUP is a key Excel function.
How to use VLOOKUP for approximate matches
In this video, we look at how to configure VLOOKUP to look up values based on an approximate match. This is good for tax rates, postage, commissions, and the like.
Why VLOOKUP is better than nested IFs
In this video, we look at a few reasons why VLOOKUP is a better option than nested IF statements.

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.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.