array formula: return next date from list

[[email protected]]

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]

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

"[email protected]" 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?
>
>

[Maistrye]

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

"[email protected]" 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