Find a previous date on a list closest to a specified date

Question

I would like to have VBA code or formula that will take a variable date value and find it on a list range of dates. If it can't find the date I want it to find the closest previous date value. VLOOKUP works great for finding an exact match but I am struggling with the code to find the closest previous date.

My only thought is to create a loop where if it doesn't match it continues to subtract a day from the variable date until it can locate a match on the list. That does not seem like the best or most efficient method and I hope someone can recommend a simpler solution.

Example:

Variable Date = 01/26/2014

Date List
02/04/2014
01/27/2014
01/24/2014
01/13/2014
12/29/2013

Desired Result = 01/24/2014

Any help would be appreciated.

Answer 1

Suppose your list of dates was in cells A1:A5 and your check date was in cell C1, you could enter this function ENTERED AS AN ARRAY FORMULA:

=MAX(($A$1:$A$5<=C1)*A1:A5)

Rememeber, to enter it as an array formula, hit Ctrl + Shift + Enter when entering the formula.

Hope this does the trick!!