Sorry for the re-post, subject line was wrong.

In columns A & B, I have a list of dates and values, such as:

1/1/1998 12.7
3/10/1998 10.4
7/24/1998 14.6
8/3/1998 7.2
8/17/1998 42.4
12/10/1998 3.3
2/20/1999 12.7


Is there a formula that, upon entering a date, will look up the value
corresponding to the next date in the list (if there's no exact match)?
For example, if I enter 3/24/1998 in, say, cell D1, the formula would
return 14.6. If I can't get the value, can I at least use INDEX to get
the location of the next date?

Marcelo's solution to your previous post works. You don't need an array
formula. Here is an alternate solution (data is in A6:B12, date to search for
is entered in C3):

=OFFSET(A6,MATCH(C3,A6:A12,1),1)

Hope this helps,

Hutch

" wrote:

Sorry for the re-post, subject line was wrong.

In columns A & B, I have a list of dates and values, such as:

1/1/1998 12.7
3/10/1998 10.4
7/24/1998 14.6
8/3/1998 7.2
8/17/1998 42.4
12/10/1998 3.3
2/20/1999 12.7


Is there a formula that, upon entering a date, will look up the value
corresponding to the next date in the list (if there's no exact match)?
For example, if I enter 3/24/1998 in, say, cell D1, the formula would
return 14.6. If I can't get the value, can I at least use INDEX to get
the location of the next date?

Tom Hutchins Wrote:
Marcelo's solution to your previous post works. You don't need an array
formula. Here is an alternate solution (data is in A6:B12, date to
search for
is entered in C3):

=OFFSET(A6,MATCH(C3,A6:A12,1),1)

Hope this helps,

Hutch

" wrote:

Sorry for the re-post, subject line was wrong.

In columns A & B, I have a list of dates and values, such as:

1/1/1998 12.7
3/10/1998 10.4
7/24/1998 14.6
8/3/1998 7.2
8/17/1998 42.4
12/10/1998 3.3
2/20/1999 12.7


Is there a formula that, upon entering a date, will look up the
value
corresponding to the next date in the list (if there's no exact
match)?
For example, if I enter 3/24/1998 in, say, cell D1, the formula
would
return 14.6. If I can't get the value, can I at least use INDEX to
get
the location of the next date?



=OFFSET(A6,MATCH(C3,A6:A12,1)-IF(ISERROR(MATCH(C3,A6:A12,0)),0,1),1)

This should adjust what Hutch quoted to allow for an exact match.

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=563047