Basic Tax Rate calculation with VLOOKUP

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.

How this formula works

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.