Lookup the nearest date
Array formula in E3:
Recommended articles:
Elizabeth asks: Hi Oscar, Need help with a formula Please. I need to figure out the date range of a [โฆ]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when [โฆ]
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
Learn more about array formulas:
Learn the basics of Excel arrays
Array formulas allows you to do advanced calculations not possible with regular formulas.
Explaining array formula in cell E3
You can easily follow along, select cell E3. Go to tab "Formulas" and click on "Evaluate formula".
Click "Evaluate" button to move to next step.
Step 1  Subtract dates with search date
A1:A10$E$1
becomes
{39814; 39830; 39822; 39842; 39834; 39830; 39816; 39817; 39824; 39830}39828
and returns {14;2;6;14;6;2;12;11;4;2}
Step 2  Convert numerical values to absolute values
ABS(A1:A10$E$1)
becomes
ABS({14;2;6;14;6;2;12;11;4;2})
and returns {14;2;6;14;6;2;12;11;4;2}
Step 3  Find smallest numerical value in array
MIN(ABS(A1:A10$E$1))
becomes
MIN({14;2;6;14;6;2;12;11;4;2})
and returns 2.
Step 4  Find position in array
MATCH(MIN(ABS(A1:A10$E$1)), ABS(A1:A10$E$1), 0)
becomes
MATCH(2, ABS(A1:A10$E$1), 0)
becomes
MATCH(2, {14;2;6;14;6;2;12;11;4;2}, 0)
and returns 2. Numerical value 2 has position 2 in the array.
Learn more about the MATCH function:
Identify the position of a value in an array.
Step 5  Return value
INDEX(A1:A10, MATCH(MIN(ABS(A1:A10$E$1)), ABS(A1:A10$E$1), 0))
becomes
INDEX(A1:A10, 2)
becomes
INDEX({39814;39830;39822; 39842;39834;39830; 39816;39817;39824;39830}, 2)
an returns 39830 or 1172009 in cell E3.
Learn more about the INDEX function:
Gets a value in a specific cell range based on a row and column number.
Download excel example file
findnearestdate.xls
(Excel 972003 Workbook *.xls)
Recommended articles:
Lookup min max values within a date range
This post demonstrates how to find minimum and maximum value using two conditions. In this case they are date conditions [โฆ]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download [โฆ]
Highlight overlapping date ranges using conditional formatting
How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula [โฆ]42 Responses to โLookup the nearest dateโ
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Contact Oscar
You can contact me through this webpage
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))}
What if I need to return a date equal to or greater than the search date instead of the "nearest date"? Then, what if the dates don't contain a date that is equal or greater than the search date, can I pull back a blank?
Jeremy,
Array formula:
=INDEX(A1:A10, MATCH(MIN(IF(A1:A10$D$1>=0, A1:A10$D$1, "")), A1:A10$D$1, 0))
Thank you for commenting!
Fun fact I am proud of, as this thread introduced me to the array formula concept just a couple of hours ago, and it took a while to understand what parts of the formula returned what values. I wanted to use this to find nearest future date from a horizontal array (3 columns, 1 row). I made it work in your vertical array example by the array formula: =INDEX(A1:A10,MATCH(0,A1:A10$E$1,1),1). Then I copied, transposed, and pasted the date array into A12:J12 and changed the formula to: =INDEX(A12:J12,1,MATCH(0,A12:J12$E$1,1)). YAY!
Kris,
I wanted to use this to find nearest future date from a horizontal array (3 columns, 1 row). I made it work in your vertical array example by the array formula: =INDEX(A1:A10,MATCH(0,A1:A10$E$1,1),1)
Yes, you donยดt need the ABS function in your example. Great!
Hi Oscar
I have a similar problem but with a twist.I have two worksheets and I need to lookup a date in a nearest date range.y data looks soething like this.
Data Date Data Date Result date
A 1/1/2013 A 1/5/2013 1/1/2013
A 2/1/2013 B 4/6/2013 4/1/2013
A 3/1/2013
B 4/1/2013
B 5/1/2013
So as you can see the formulae forst needs to match the two data columns like(AA)and only then pick the nearest date corresponding to dates pertainig to that data point.
Hope I am clear.Kindly request you to help
Murtuza Vasanwala,
I am sorry, I donยดt understand. Can you describe in greater detail?
Hi Oscar sorry for such a late reply but let me explain myself in greater detail.Basically I have two columns of data containing the same entries so assume column A and column C are two such columns.and column B and column D have certain dates corresponding to these entries.I need a formulae which will first match the entries in column A wih entries in column C .then compare the date in column D with the dates in column B and then throw up the nearest date.So my data sheet looks something like this:
Col A Col B Col C Col D Col E(result)
A 1/2/2013 A 5/1/2013 4/3/2013
A 2/2/2013 B 5/2/2013 5/5/2013
A 4/3/2013
B 5/5/2013
B 9/10/2013
As you can see the formulae first compares the entries in col C (A) with the entries in col A (all the A's) then it matches the date in col D (5/1/2013) with the dates pertaining to value A in col B.The closest date then is 4/3/2013 which is the answer.
Hope I have made myself amply clear this time waiting for your response
Murtuza vasanwala,
Read this:
https://www.getdigitalhelp.com/2007/12/05/excelfindclosestvalue/#criterion
I need to write a formula where I type in a date and it looks through a column and sees which date is it greater than but closest to.
Robert,
=INDEX(A1:A10, MATCH(MIN(ABS(If(A1:A10>=E1,A1:A10E1,""))), ABS(If(A1:A10>=E1,A1:A10E1,"")), 0))
I was trying to work out a formula similar to this in that I had 2 columns on each sheet one column had names and the other dates same style on the other sheet. I want to find dates that were closest to each other for a specific name, but didnt work out. How can I tweak this formula to pull that off? It seems a bit tricky cause I couldnt resolve what to do in place of the $E$1 cell? Any suggestions?
I have a cell at the top of my spreadsheet that mirrors the expiry date closest to today.
I am wondering if there is a way to have it link to the cell it is mirroring, rather than scrolling down through thousands of rows to find the highlighted cell in question?
To my opinion there is a much simpler NONArray formula that achieves the same:
Michael (Micky) Avidan
=LOOKUP(2,1/FREQUENCY(0,ABS(A1:A10E1)),A1:A10)
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092015)
ISRAEL
Micky,
thanks for sharing!
This worked like a charm. Thanks
Hi, I have 15 rows of varying dates. And I have a separate date on the 16th row. How can I determine the closest start date and the closest end date range that the 16th row date falls in?
Any help is much appreciated. Thank you.
Bishnu
Example:
1.01/28/16
2.10/24/16
3.01/18/17
4.01/29/18
5.06/05/18
6.08/06/18
7.09/05/18
8.01/14/19
9.07/17/19
10.01/21/20
11.09/04/20
12.09/05/22
13.01/24/23
14.01/17/24
15.01/21/25
(Above varying dates)
16.3/26/2019
(To determine where this above date falls in between closest to)
17. Start Date =09/05/18
18. End date = 07/17/19
(How do I figure out 17. and 18.) Thanks, hope this example helps to understand the problem a bit more.
@Mayetreyee,
I would suggest as following formulas:
1) To determine the Lowest Closest date:
=MAX((A1:A15=0,A1:A15))
*** Both s=are ArrayFormulas !!!

Michael (Micky) Avidan
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092015)
ISRAEL
@Mayetreyee,
Something went wrong in my previous reply.
I would suggest as following formulas:
1) To determine the Lowest Closest date:
=MAX((A1:A15=0,A1:A15))
2) To determine the Highest Closest date:
=MIN(IF(A1:A15A16>=0,A1:A15))
*** Both are ArrayFormulas !!!

Michael (Micky) Avidan
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092015)
ISRAEL
I went about this a little differently, no arrays needed
Find how many numbers are bigger then the one you are looking for with CountIf()
Then I used =Large this will find the nth dates in a list we have the nth we are looking for in the countIF()
=LARGE(A:A,COUNTIF(A:A,">="&TODAY()))
for the highest closest date =LARGE(A:A,COUNTIF(A:A,">="&TODAY()+1))
for the lowest closest date =LARGE(A:A,COUNTIF(A:A,">="&TODAY()1))
https://www.excelireland.com
Dear all,
your advices are really helpful, I need the opposite of the comment (Jan, 30,2014) so smaller but closet to.
Thanks
Thanks for sharing how to find the nearest date. But I'm stuck with another condition that is I have another lookup value (some user ids) and only if that matches then I have to search for the nearest newest dates for that row. Please note that there are duplicate user ids.
Regards,
Ehsan
Hi Oscar
I am trying to lookup the nearest activity date to a target date based on ID number. For ID 01 the closest date would be 05/07/2016. Therefor everytime the ID changes the range to lookup and compare will be different. Are you able to advise on solving this task.
ID Target Date ActvityDate
01 12/07/2016 05/07/2016
01 12/07/2016 15/08/2016
01 12/07/2016 10/06/2016
02 11/07/2016 20/07/2016
02 11/07/2016 08/07/2016
02 11/07/2016 15/07/2016
Stephe
Stephen,
Array formula in cell D2:
=INDEX($C$2:$C$7, MATCH(MIN(ABS(IF(A2=$A$2:$A$7, $C$2:$C$7B2, 0B2))), ABS(IF(A2=$A$2:$A$7, $C$2:$C$7B2, 0B2)), 0))
Thanks Oscar the script seems to work well however, If the same ID has more than one Target date the formula fails to provide the closest Activity date to the Target date as it is looking at the whole range associated with the ID rather than activity date related to id and new Target date.
Stephen
Stephen,
I see, try this:
=INDEX($C$2:$C$7, MATCH(MIN(ABS(IF((A2=$A$2:$A$7)*(B2=$B$2:$B$7), $C$2:$C$7B2, 0B2))), ABS(IF((A2=$A$2:$A$7)*(B2=$B$2:$B$7), $C$2:$C$7B2, 0B2)), 0))
@Oscar,
Any particular reason why my suggestion was removed from this page ?

Michael (Micky) Avidan
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092017)
ISRAEL
Hi Michael
I have not removed your comment, I searched all comments marked as spam and could not find a comment from you. When did you comment?
I allow everyone to comment as long as it is not spam and I am happy that you commented. Please try again.
Hi,
As far as I recall it was 2 days ago.
My solution was presented within an attched picture.
So, here we go again:
https://s21.postimg.org/xqs2eg6s7/OSCAR_1.png

Michael (Micky) Avidan
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092017)
ISRAEL
Thanks Oscar this has worked. I am taking my time to see how the formula works. It feels almost magical. thanks again
Stephen
@Oscar,
Hmmm..., I wonder...
What would YOU prefer as a solution.
A short regular formula or שמ Array Formula almost TWICE as long ?
The fact that Stephen didn't refer to my suggestion seems a little wierd to me BUT as from you I was expecting at least for a short comment.
Here, again, is my formula:
https://s15.postimg.org/x6wsgxtl7/NONAME.png

Michael (Micky) Avidan
โMicrosoftยฎ Answers"  Wiki author & Forums Moderator
โMicrosoftยฎ" MVP โ Excel (20092017)
ISRAEL
Michael
What would YOU prefer as a solution.
A short regular formula or שמ Array Formula almost TWICE as long ?
A short regular formula, of course.
The fact that Stephen didn't refer to my suggestion seems a little wierd to me BUT as from you I was expecting at least for a short comment.
Your formula seems to only look for the ID and not the target Date, the same functionality my first array formula had. I was not sure what to say about it so I wrote nothing. But I am thankful for your comment, your approach is interesting and inspiring and a regular formula is better.
Michael,
I changed your formula so it also considers the Target Date.
Regular formula in cell E2:
=LOOKUP(2,1/FREQUENCY(0,ABS((A$2:A$7=A2)*(B$2:B$7=B2)*(C$2:C$7)INDEX(B:B,MATCH(A2,A:A)))),C$2:C$7)
Thanks again.
Hello,
I am struggling to create a function that gives me the date one bigger than the one specified for a specific number in a different column. I need to look at a date in Table 1 and find the corresponding date in Table 2 that is just greater than that date that also matches with the number listed.
Any help would be greatly appreciated. Thank you in advance.
Example:
Table 1
Number Date
9 10/6/2016 16:15
10 10/6/2016 15:18
8 10/8/2016 11:03
9 10/12/2016 21:54
10 10/12/2016 21:17
8 10/15/2016 14:14
Table 2
Reading Date Number
10/6/2016 19:11:15 8
10/6/2016 19:11:25 9
10/6/2016 19:11:35 10
10/7/2016 18:29:07 8
10/7/2016 18:29:12 9
10/7/2016 18:29:15 10
10/7/2016 20:37:12 8
10/7/2016 20:38:55 9
10/7/2016 20:39:45 10
10/9/2016 14:06:32 8
10/9/2016 14:06:45 9
10/9/2016 14:06:59 10
10/9/2016 20:36:32 8
10/9/2016 20:36:51 9
10/9/2016 20:37:19 10
10/10/2016 11:34:40 8
10/10/2016 11:34:05 9
10/10/2016 11:34:11 10
10/11/2016 5:34:19 8
10/11/2016 5:34:14 9
10/11/2016 5:34:33 10
10/11/2016 17:24:20 8
10/11/2016 17:24:50 9
10/11/2016 17:24:44 10
10/12/2016 13:14:21 8
10/12/2016 13:14:12 9
10/12/2016 13:18:17 10
10/13/2016 15:47:49 8
10/13/2016 15:47:16 9
10/13/2016 15:47:20 10
10/14/2016 4:22:14 8
Tyler
Array formula in cell C1:
=MIN(IF(($A1<$F$2:$F$32)*($B1=$E$2:$E$32),$F$2:$F$32,""))
Thank you very much!
U all are really amazing I am working as Demand Planner and all ur formulas really helpful to me.
Ravi
Thank you for commenting.