Returning closest prior date

[Chuck]

I need a formula which will return the closest prior date to today's date
from a list. For example, from the following list and today being Oct-22-04,

Row 1: 7/1/04
Row 2: 10/1/04
Row 3: 1/1/05

I want to return 10/1/04. Please advise. Thanks.

 

[Frank Kabel]

Hi
try
=VLOOKUP(B1,A1:A100,1,TRUE)
where B1 stores the date to search for

 

[Chuck]

Great. Thank you.

Hi
try
=VLOOKUP(B1,A1:A100,1,TRUE)
where B1 stores the date to search for

 

[Daniel.M]

Hi Frank,

=VLOOKUP(B1,A1:A100,1,TRUE)
where B1 stores the date to search for

This assumes theDates are in order (sorted).

For a solution with out of order theDates, the following ARRAY
(Ctrl-Shift-Enter) formula:

=MAX(IF(A1:A100<B1,A1:A100))

or directly:

=MAX(IF(A1:A100<TODAY(),A1:A100))

Regards,

Daniel M.

 

[Frank Kabel]

Hi Daniel

Hi Frank,


This assumes theDates are in order (sorted).

correct. Should have mentioned that restriction

Frank