Sometimes when using the function VLOOKUP (VLOOKUP) Exact matches are not needed. For example, when you want to calculate income tax. The tax rates shown in the table below apply to residents of Australia:
| Income | Tax on this income |
|---|---|
| 0 — $6000 | Zero |
| $ 6001 – $ 35000 | 15 cents for every $1 over $6000 |
| $ 35001 – $ 80000 | $4350 plus 30 cents for every $1 over $35000 |
| $ 80001 – $ 180000 | $17850 plus 38 cents for every $1 over $80000 |
| $ 180001 and more | $55850 plus 45 cents for every $1 over $180000 |
Example: If the income is $37000, then the tax will be: 4350+0,3*(37000-35000) = 4350+600 = $ 4950.
To automatically calculate income tax, follow these steps:
- To the list Sheet 2, create a range like in the image below and name it “Rates”.
- We already know that when the fourth function argument VLOOKUP (VLOOKUP) is FALSE, it can return an exact match or an error #AT (#N/A) if it can’t find it. If the argument is TRUE (TRUE), the function will return an exact match, and if it does not find it, then the largest value that is less than the desired one (A2). This is exactly what we need!
=VLOOKUP(A2,Rates,2,TRUE)=ВПР(A2;Rates;2;ИСТИНА)Explanation: Although Excel cannot find 37000 in the first column of the “Rates” range, it can still find 35000 (the largest value that is less than 37000). As a result, the program returns 4350.
- Now it remains for us to solve the second part of the equation: +0,3 * (37000-35000). This is very easy to do.
To find out the amount of tax that is charged for each dollar (i.e. 0,3), you need to assign to the third argument of the function VLOOKUP (VLOOKUP) value 3. And to return 35000, you need to assign the value to the third argument of the function 1. The formula below does this trick:
=VLOOKUP(A2,Rates,2,TRUE)+VLOOKUP(A2,Rates,3,TRUE)*(A2-VLOOKUP(A2,Rates,1,TRUE))=ВПР(A2;Rates;2;ИСТИНА)+ВПР(A2;Rates;3;ИСТИНА)*(A2-ВПР(A2;Rates;1;ИСТИНА))
Note: To assign the fourth function argument VLOOKUP (VLOOKUP) TRUE, the first column of the table must be sorted in ascending order.