Excel Multiple IF Conditions - VLOOKUP

When we need to solve some problems that involve in many “IF Conditions” (generally more than 4 “IF Conditions”), it is more convenient for us to use VLOOKUP using Multiple IF function. Here is an example:

Design an excel function that returns the marginal tax rate of the income:

When the income < 15,000, the marginal tax rate is 0%;

When income is between 15,000 and 50,000 , the marginal tax rate is 20%;

When income is between 50,000 and 100,000 , the marginal tax rate is 30%;

When income is above 100,000 , the marginal tax rate is 40%.

I turned this description into a simple table:

Now, there is another table which contains five people and their incomes, I want to calculate their marginal tax rate based on table ”Standard”.

First, we need to write down “=VLOOKUP()”, this means we are going to use VLOOKUP to solve this problem. Then we click one of the name in Income line of table “Marginal Tax Rate Calculation” and find Income in table “Standard”, we regard Income column in table “Standard” as the leftmost to circle a range which includes both Income and Marginal Tax Rate, click F4 (or Fn + F4) on your keyboard to fix query area. We want to calculate Marginal Tax Rate in table “Marginal Tax Rate Calculation”, from table “Standard”, we can see that “Marginal Tax Rate” is in the second column, so we write ”2” as following. You may notice that there is a “0” at the end of this formula, it means exact match.

FUNTERN Associate: Jin Wang