I would like to have a VBA code or formula that takes the value of a date variable and finds it in a list of date ranges. If he cannot find the date, I want her to find the closest value to the previous date. VLOOKUPgreat for finding an exact match, but I'm struggling with the code to find the closest previous date.
My only thought is to create a loop where, if it does not match, it continues to subtract the day from the date of the variable until it finds a match in the list. This does not seem to be 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.
source
share