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

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top