vlookup, sumproduct, hlookup, index match, not sure
 

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC

vlookup, sumproduct, hlookup, index match, not sure
 

try:

=INDEX(A3:A500,MATCH(TODAY(),A3:A500,0)-1)

HTH

"SteveC" wrote:

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC

vlookup, sumproduct, hlookup, index match, not sure
 

Try something like this:

With
A3:A500 containing dates (one of them is today's date)

This formula returns the value of the cell immediately above the first
occurrence of today's date

B1: =INDEX(A3:A500,MATCH(TODAY(),A3:A500,0)-1)

Note: If today's date is not in the list or it is the first item, the
formula returns an error.
We can trap that error if you like....

This formula returns "No Match" if today's date is not in the list.
and it returns today's date if it is the first item.
B1:
=IF(COUNTIF(A3:A500,TODAY()),INDEX(A3:A500,MAX(MAT CH(TODAY(),A3:A500,0)-1,1)),"No match")


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"SteveC" wrote:

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC

vlookup, sumproduct, hlookup, index match, not sure
 

=index(a1:a100,match(today()-1,a1:a100,0)-1)



SteveC wrote:

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC

--

Dave Peterson

vlookup, sumproduct, hlookup, index match, not sure
 

Works great, thank you!

"Toppers" wrote:

try:

=INDEX(A3:A500,MATCH(TODAY(),A3:A500,0)-1)

HTH

"SteveC" wrote:

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC

vlookup, sumproduct, hlookup, index match, not sure
 

ron, that works as well (think it's the same as toppers above). Thanks very
much for your time and the additional trap code.

"Ron Coderre" wrote:

Try something like this:

With
A3:A500 containing dates (one of them is today's date)

This formula returns the value of the cell immediately above the first
occurrence of today's date

B1: =INDEX(A3:A500,MATCH(TODAY(),A3:A500,0)-1)

Note: If today's date is not in the list or it is the first item, the
formula returns an error.
We can trap that error if you like....

This formula returns "No Match" if today's date is not in the list.
and it returns today's date if it is the first item.
B1:
=IF(COUNTIF(A3:A500,TODAY()),INDEX(A3:A500,MAX(MAT CH(TODAY(),A3:A500,0)-1,1)),"No match")


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"SteveC" wrote:

Hi, in Column A I have dates A3:A500. In one of those cells -- it could be
almost any one -- is today's date -1. So let's say for the data range i'm
using, the today's date -1 is in cell A300, and its 2/14/2007.

How do I pull in the date value immediately above in cell a299? It's not
necesarily 2/13/2007 -- it could be 2/10/2007 or whatever...

thanks... not sure what function to use or to start with. Thanks very much
for your help.

SteveC