Compare a value to a list of values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Jamie said:
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
Click to expand...


Hi Jamie,

If I well understand (but I'm not sure...), you can use the SUMPRODUCT
function:

=SUMPRODUCT((First_dat_range>=your_date)*(First_dat_range<=your_date);(your_value_range))



--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Back
Top