Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .