Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
Lookup Value in Range/Array and Return Column Header Value | Excel Discussion (Misc queries) | |||
Lookup Value in Range/Array and Return Column Header Value | Excel Worksheet Functions | |||
2 column lookup - match to date range | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions |