In the file you downloaded look for the sheet name ‘VLOOKUP-Bands-2’
In this example you are calculating the tax liability based on various salaries. The tax table is provided (D20 to F26) and a series of salaries are provided (A30 to A34). You need to calculate what the tax liability will be on these amounts.
- In B30 use VLOOKUP to determine what the fixed portion will be based on the salary in A30 and the tables shown in the A30 table
- In C30 use VLOOKUP to determine what the % for that salary level
- In D30 use VLOOKUP to determine what the lower limit is of the band (HINT: You can use 1 as your column number)
- In E30 calculate the amount to apply the % to e.g. a salary of R150 000 would be R150 000 less the band it falls into (R140 000) so R10 000.
- In F30 calculate the % portion of the tax (based on E30 and C30)
- In G30 pull through the fixed portion from B30
- In H30 add the % portion and fixed portion together to get a total tax bill
- Copy down to row 34
- In H35 calculate the total tax bill and compare it to H36. If they are not the same find out what the problem is
Video Instructions
To follow along go to 5_4_1
Video Solution
To follow along go to 5_4_2