The tax brackets can be confusing to work with. Different ‘levels’ of your income needs to be multiplied with a different percentage to find the total. This can be done with the conventional Excel formulas like IF and a bunch of others, but that’s the long and hard to read approach. VLOOKUP with approximate match mode becomes a great tool to get values placed within brackets and to calculate Basic Tax Rate.
Update: XLOOKUP alternative has been added.
Syntax
=VLOOKUP(your income, tax rate table, column position of tax, TRUE)
=XLOOKUP(your income, income column, tax rates column,,-1)
Steps
- Start with = VLOOKUP( function
- Select or type the reference that contains your salary F5,
- Continue with the reference that contains the value B4:C9,
- Type the column position of tax rates from left 2
- Type ) to close and complete the formula
How
The VLOOKUP has 2 modes:
- Exact match
- Approximate match
While exact match searches for the exact value, approximate searches for the closest value. Because most of the salaries are unique values, we need to find the closest value in a tax rate table to calculate Basic Tax Rate. The VLOOKUP has 4 arguments:
- Value we search
- Range of cells we search for the value
- Column number that contains values we want
- Match type that gets TRUE for approximate match and FALSE for exact match
=VLOOKUP(F5,B4:C9,2,TRUE)
For example; the VLOOKUP searches salary of 36,000 in first column of the tax rate table. Because there isn't any value equals to 36,000, the VLOOKUP locates the closest lower value and returns the corresponding rate, 14%.
Important Note: The approximate-match VLOOKUP assumes that table's first column is sorted. You may see mismatches with unsorted tables.
Also see the related article using SUMPRODUCT to do your taxes.
