Basic Tax Rate calculation with VLOOKUP
=VLOOKUP(amount,tax_table,2,TRUE)
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.
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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
