Finding last appearance of data
 

I have several columns of data, with column A as monthly dates. I would like
to find and return the last date at which the data was equal to or less than
the value at the most recent date. For example, the function should return
"February 2006" for the below data.

January 2006 178.17
February 2006 176.27
March 2006 177.11
April 2006 177.61
May 2006 178.61
June 2006 177.90
July 2006 177.79
August 2006 177.29
September 2006 176.37

I got this to work using =INDEX($A$3:$A$239,MATCH(b239,b3:b239,1)), but when
I substituted different values for the most recent value, the formula did not
return the correct date.

It would also be great to make the formula dynamic, so it would update as
additional months of data are added. I have used the OFFSET function to
define named ranges, but this doesn't seem to work.

Thank you

Finding last appearance of data
 

try this copied down
=INDEX(A12:A19,MATCH(B20,B12:B19))
or
=INDEX(A13:A20,MATCH(VLOOKUP(99999,B:B,1),B13:B20) )
--
Don Guillett
SalesAid Software

"hello" wrote in message
...
I have several columns of data, with column A as monthly dates. I would
like
to find and return the last date at which the data was equal to or less
than
the value at the most recent date. For example, the function should return
"February 2006" for the below data.

January 2006 178.17
February 2006 176.27
March 2006 177.11
April 2006 177.61
May 2006 178.61
June 2006 177.90
July 2006 177.79
August 2006 177.29
September 2006 176.37

I got this to work using =INDEX($A$3:$A$239,MATCH(b239,b3:b239,1)), but
when
I substituted different values for the most recent value, the formula did
not
return the correct date.

It would also be great to make the formula dynamic, so it would update as
additional months of data are added. I have used the OFFSET function to
define named ranges, but this doesn't seem to work.

Thank you

Finding last appearance of data
 

Thank you -- both of these formulas worked, but I have not been able to
substitute dynamic ranges for the referenced ranges in the formula. Shouldn't
that just be a simple matter of defining dynamic ranges using OFFSET, and
substituting these range names into the formula?

Many thanks

"Don Guillett" wrote:

try this copied down
=INDEX(A12:A19,MATCH(B20,B12:B19))
or
=INDEX(A13:A20,MATCH(VLOOKUP(99999,B:B,1),B13:B20) )
--
Don Guillett
SalesAid Software

"hello" wrote in message
...
I have several columns of data, with column A as monthly dates. I would
like
to find and return the last date at which the data was equal to or less
than
the value at the most recent date. For example, the function should return
"February 2006" for the below data.

January 2006 178.17
February 2006 176.27
March 2006 177.11
April 2006 177.61
May 2006 178.61
June 2006 177.90
July 2006 177.79
August 2006 177.29
September 2006 176.37

I got this to work using =INDEX($A$3:$A$239,MATCH(b239,b3:b239,1)), but
when
I substituted different values for the most recent value, the formula did
not
return the correct date.

It would also be great to make the formula dynamic, so it would update as
additional months of data are added. I have used the OFFSET function to
define named ranges, but this doesn't seem to work.

Thank you