return previous date from list

[[email protected]]

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.

[Marcelo]

hi Barbetta,

try,

=index(b1:b7,match(d1,a1:a7,1)+1)

hth
regards from Brazil
Marcelo

"[email protected]" escreveu:

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

[Marcelo]

hi Barbetta,

try,

=index(b1:b7,match(d1,a1:a7,1)+1)

hth
regards from Brazil
Marcelo


"[email protected]" escreveu:

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

[Ron Coderre]

Try something like this:

D1: (a date to find)
E1:
=IF(ISNA(VLOOKUP(D1,A1:A7,B1:B7,0)),INDEX(B1:B7,MATCH(D1,A1:A7,1)+1),VLOOKUP(D1,A1:B7,2,0))

If the formula finds an exact match in Col_A, it uses the respective value
from Col_B. Otherwise, it pulls the Col_B value from the next row.


Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"[email protected]" wrote:

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

[Domenic]

Assuming that A2:B8 contains the data, try...

=INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<>D2))

To return 12.7 for dates earlier than 1/1/98, try the following formula
instead...

=IF(D2<>"",IF(D2>A2,INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<>D2)),B
2),"")

Hope this helps!

In article <[email protected]>,
[email protected] wrote:

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

[[email protected]]

Thanks everyone!

Domenic wrote:
> Assuming that A2:B8 contains the data, try...
>
> =INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<>D2))
>
> To return 12.7 for dates earlier than 1/1/98, try the following formula
> instead...
>
> =IF(D2<>"",IF(D2>A2,INDEX(B2:B8,MATCH(D2,A2:A8)+(LOOKUP(D2,A2:A8)<>D2)),B
> 2),"")
>
> Hope this helps!
>
> In article <[email protected]>,
> [email protected] wrote:
>
> > 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.