Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare a value to a list of values
I have a table that looks like this
First date Last Date QValue H Value 2006-01-01 2006-04-26 10 3 2006-04-26 11 4 2006-04-27 12 5 2006-04-28 13 6 The problem is people enter data into the table 2 different ways, some people use first date and last date and the values as in row 1 above. Other people will date fill down so each day appears in the first date column with the values as in lines 2,3,and 4. Some people could do it both ways like the table above, since getting people to do it only one way isn't an option, I am trying to create a formula that can search the table regardless of which method people use to input data. I need a formula that can take 2006-01-02 and look for it in the first date column and recognize that 2006-01-02 falls between 2006-01-01 and 2006-04-26, and return the corresponding value. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Compare a value to a list of values
Jamie wrote:
I have a table that looks like this First date Last Date QValue H Value 2006-01-01 2006-04-26 10 3 2006-04-26 11 4 2006-04-27 12 5 2006-04-28 13 6 The problem is people enter data into the table 2 different ways, some people use first date and last date and the values as in row 1 above. Other people will date fill down so each day appears in the first date column with the values as in lines 2,3,and 4. Some people could do it both ways like the table above, since getting people to do it only one way isn't an option, I am trying to create a formula that can search the table regardless of which method people use to input data. I need a formula that can take 2006-01-02 and look for it in the first date column and recognize that 2006-01-02 falls between 2006-01-01 and 2006-04-26, and return the corresponding value. Thanks Hi Jamie, If I well understand (but I'm not sure...), you can use the SUMPRODUCT function: =SUMPRODUCT((First_dat_range=your_date)*(First_da t_range<=your_date);(your_value_range)) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare values in auto filter | Excel Worksheet Functions | |||
How do I generate a list from a range of values | Excel Worksheet Functions | |||
if specific value from list A equals one of the values from list b... | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) |