VLOOKUP Formula

[AnilR]

Hi:
I'm looking for a VLOOKUP or any other kind of formula that would give me either 0%, 50%, or 85% depending on a couple of criterias, Status and Income.

An example of the logic for a person whose status is "Single" and "Income" level is say, $95000.00 is as follows:

If Single AND Income less than $25000, then 0%
If Single AND Income is between $25000 and $34000, then 50%
If Single AND Income is greater than $34000, then 85%

Since this person's is "Single" and his/her Income is $95000, I'd like to be able to get 85%. But this person could be "MFJ" and Income could be between $32000 and $44000. In this case the result should be 50%

Here is an image:



Please see the attached Spreadsheet.

Thanks and much appreciation for your expert assistance.

 

[p45cal]

See attached.
I changed your table to show only threshold levels.
Formula in cell E2:

=INDEX($C$6:$E$6,MATCH($C$3,INDEX($C$7:$E$11,MATCH(C2,$B$7:$B$11,0),0)))

Note that to get exactly the same results as you want, I needed to add 0.001 to the values in E7:E11.
Conventionally, your:
If Single AND Income < $25000, then 0%
If Single AND Income is between $25000 and $34000, then 50%
If Single AND Income is above $34000, then 85%
would be:
If Single AND Income < $25000, then 0%
If Single AND Income is >= $25000 and < $34000, then 50%
If Single AND Income is >= $34000, then 85%

To keep the formula simple the attached really says:
If Single AND Income < $25000, then 0%
If Single AND Income is >= $25000 and < $34000.001, then 50%
If Single AND Income is >= $34000.001, then 85%

 

[AnilR]

Thank you very much. Just what the doctor ordered.

Cheers!

 

[bosco_yip]

1] Just keep your original Lookup Table

2] In E2, enter formula :

=LOOKUP(C3,0+MID(INDEX(C7:E11,MATCH(C2,B7:B11,0),0),{8,2,4},5),C6:E6)

Regards
Bosco

 

[AnilR]

It works. You are a genius! Thanks. Much appreciated.