up vote 1 down vote favorite
share [g+] share [fb]

enter image description here

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?

link|improve this question
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. – AdamV Aug 5 '11 at 14:01
feedback

1 Answer

up vote 4 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.