Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|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
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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