Help figuring out formula for multiple lookup values in a range

[albardit18]

Hello,

I am having trouble trying to figure out a formula that will ultimately bring back one value by looking up two values in an array of data.


A B C D

A 1 4 $10.00
A 4 10 $20.00
A 10 20 $30.00
B 1 4 $15.00
B 4 10 $25.00
B 10 20 $35.00
C 1 4 $17.00
C 4 10 $27.00
C 10 20 $37.00

Values to lookup
A 6
B 15
C 2


The data on the left is the table that needs to be looked up from. Basically for the first example I am trying to find the dollar amount for A and the range that 6 falls in.. which would be $20.00. For the second set I need to find B and the range that 15 falls in.. which would be $35.00.

I already have a formula to find the row number in a range of data, but that becomes useless when there are multiple ranges with different coefficients such as A, B, and C.

=SUMPRODUCT(--(B4:B6<=H4)*(C4:C6>=H4),ROW(B4:B6))-3

[NBVC]

Assuming the criteria range is in E1:F3, then in G1 enter:

=INDEX($D$1:$D$9,MATCH(1,INDEX(($A$1:$A$9=E1)*($C$1:$C$9>=F1),0),0))

copied down.

Note that this only uses the upper bounds in column C, but your lower bounds in column B overlap..

[albardit18]

thanks a lot man, it worked perfect :D

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[albardit18]

will do
thanks