3 column lookup within range

[cmgmyr]

I've looked all over the internet to find something without much
luck...I'm hoping I'm just missing something.

I have a table with 3 columns (start, end, price). I would like to
take a value (C4) and see if it's get the value out of the table.

Start | End | Price
0 | 1 | $9.95
2 | 10 | 5.00
11 | 20 | 4.75

if cell C4 = 5, I need to return the value of $5.00, if cell C4 = 20,
I need to return the value of $4.75...I think you get the point.

Please let me know if you need me to clarify anything. Thanks in
advance for the help.
-Chris

 

[Bob Phillips]

=SUMPRODUCT(--(A6>=$A$2:$A$4),--(A6<=$B$2:$B$4),$C$2:$C$4)

I am assuming the test value is in A6 not C4 as C4 is part of the table.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[cmgmyr]

=SUMPRODUCT(--(A6>=$A$2:$A$4),--(A6<=$B$2:$B$4),$C$2:$C$4)

I am assuming the test value is in A6 not C4 as C4 is part of the table.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -

Thank you very much, that worked like a charm! Here is the final
formula:
=SUMPRODUCT(--(C4>=$K$5:$K$13),--(C4<=$L$5:$L$13),$M$5:$M$13)