3 column lookup within range
 

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

3 column lookup within range
 

=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" wrote in message
...
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

3 column lookup within range
 

On Jan 16, 5:52*am, "Bob Phillips" wrote:
=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" wrote in message

...



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- Hide quoted text -

- 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)