Excel IF and VLOOKUP function help

Question

The exercise is to use IF() and VLOOKUP() functions to return the tax rate based on the annual income and number of people in the household.

So far I've tried inputting:

=IF(AND(B$2=3,C$2<=35000),VLOOKUP(C2,'Tax Index'!A3:E8,5))

but the third attribute in VLOOKUP(), or the col_index_num needs to be changed in order to return the correct tax rate, but that is manual work and isn't the point of the exercise.

Can anyone help me go through this problem?

Solution

The third parameter of the VLOOKUP() function is the column of the table you want to address. So since for 1,2,3 you want columns 3,4,5 then you just need to add 2 to the Type of Household parameter, like so:

=IF(AND(B$2=3,C$2<=35000),VLOOKUP(C2,'Tax Index'!A3:E8,B2+2))

so for your first line, the Type of Household of 3 would give you column 5 of the table.

Comments

[TheModularMind]

Is there a good reason you can't combine the reference tables into one, since one is for income up to 35,000 and one from 35,000 upwards, then split again at 100,000. This would avoid the IF altogether, just let VLOOKUP do it's thing. Other than that, Lance is on the money with a calculation for the column_index.