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.
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
