Finding the nearest date in a range of dates using excel formula
Formula in E3:
How to enter an array formula
- Select cell E3
- Type or copy/paste above array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
Download excel example file
find-nearest-date.xls
(Excel 97-2003 Workbook *.xls)
Functions:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ABS(number)
Returns the absolute value of a number, a number without its sign.
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
Related posts:
Sort dates within a date range using excel array formula
Formula for matching a date within a date range in excel
Extract dates and adjacent value in a range using a date critera in excel
Recommended categories
This is absolutely great function. Many thanks for this.
However, if the any of the datevalues are blank or not properly formatted. The function wont work.
Perhaps could be easily fixed.
This has saved days of my work. I really appreciate your several contributions specially with Array functions.
Best wishes
Aziz
Aziz,
The formula works with blank datevalues.
Thank you for commenting!
Hi Oscar
i downloaded the excel file you provided and copied the exactly same formula =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)).....it simply doesnt work for me, what is wrong with my input??
i would be appreciated if you could help ..do i hve to be mindful some settings?
Andrew,
You need to enter the formula as an array formula. Sorry for not being clear.
1. Paste the formula to the formula bar
2. Press and hold CTRL + SHIFT
3. Press Enter simultaneously
4. Release all keys
The formula in the formula bar should now have curly brackets, like this:
{=INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0))}