array formula: return next date from list

  • Thread starter Thread starter barbetta3141
  • Start date Start date
B

barbetta3141

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
 
Tom said:
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 t
search for
is entered in C3):

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

Hope this helps,

Hutch
Click to expand...

=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.

Scot
 
Back
Top