Lookup the nearest date
Table of Contents
1. Lookup the nearest date
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the date in cell E2.
Array formula in E4:
Recommended articles:
Recommended articles
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
Recommended articles
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
How to enter an array formula
- Select cell E4
- Type or copy/paste above array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
Learn more about array formulas:
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
Explaining array formula in cell E4
You can easily follow along, select cell E3. Go to tab "Formulas" and press with left mouse button on "Evaluate formula".
Press with left mouse button on "Evaluate" button to move to next step.
Step 1 - Subtract dates with search date
B3:B12-$E$2
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
The ABS function converts a negative number to a positive.
ABS(B3:B12-$E$2)
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
The MIN function returns the smallest number in a cell range or array.
MIN(ABS(B3:B12-$E$2))
becomes
MIN({14;2;6;14;6;2;12;11;4;2})
and returns 2.
Step 4 - Find position in array
The MATCH function returns the relative position of a given value in an array or cell range.
MATCH(MIN(ABS(B3:B12-$E$2)), ABS(B3:B12-$E$2), 0)
becomes
MATCH(2, ABS(B3:B12-$E$2), 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:
Recommended articles
Identify the position of a value in an array.
Step 5 - Return value
INDEX(B3:B12, MATCH(MIN(ABS(B3:B12-$E$2)), ABS(B3:B12-$E$2), 0))
becomes
INDEX(B3:B12, 2)
becomes
INDEX({39814;39830;39822; 39842;39834;39830; 39816;39817;39824;39830}, 2)
an returns 39830 or 1-17-2009 in cell E4.
Learn more about the INDEX function:
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Get excel example file
find-nearest-date.xls
(Excel 97-2003 Workbook *.xls)
Recommended articles:
Recommended articles
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
Recommended articles
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
2. Lookup min max values within a date range
This article explains how to find the smallest and largest value using two conditions. In this case they are date conditions but they can be whatever you like, displayed in cell C3 and E3.
The maximum value in that date range is calculated in cell C6, The minimum value in cell C7. There is also a formula that finds these values and return their corresponding date, in cell E6 and E7.
Formula in cell C6:
The MAXIFS function returns the largest number from max_range ($C$10:$C$39) based on a condition or criteria.
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The first condition $B$10:$B$39, "<="&$E$3 identifies rows whose date is smaller than or equal to date in cell E3.
The second condition $B$10:$B$39, ">="&$C$3 identifies rows whose date is larger than or equal to date in cell C3.
Formula in cell C7:
The MINIFS function works exactly the same as the MAXIFS function, however, the smallest number is instead returned.
Formula in cell E6:
This formula is a simple lookup formula, it returns only a single value from column B if the value in column C matches the contents of cell C6.
INDEX($B$10:$B$39,MATCH(C6,$C$10:$C$39,0))
The MATCH function returns the relative position of a value in a cell range or array.
INDEX($B$10:$B$39,MATCH(1030.98,$C$10:$C$39,0))
becomes
INDEX($B$10:$B$39,5)
The INDEX function returns a value based on a row (and column number if needed).
and returns 8-27-09 in cell E6.
Formula in cell E7:
Get excel *.xlsx file
Lookup min max values in a date range.xlsx
Dates category
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Excel categories
52 Responses to “Lookup the nearest date”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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 opened the excel file you provided and copied the exact same formula =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)).....it simply doesn't work for me, what is wrong with my input??
I would be appreciated if you could help ..do I have to be mindful of 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(A-A)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.get-digital-help.com/2007/12/05/excel-find-closest-value/#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:A10-E1,""))), ABS(If(A1:A10>=E1,A1:A10-E1,"")), 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 NON-Array formula that achieves the same:
Michael (Micky) Avidan
=LOOKUP(2,1/FREQUENCY(0,ABS(A1:A10-E1)),A1:A10)
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Micky,
thanks for sharing!
This worked like a charm. Thanks
How would you go about doing the above while excluding any values that happen to be zero?
Thomas
=MIN(IF((Date_col<=$E$3)*(Date_col>=$C$3)*(Close_col<>0), Close_col, ""))
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 Array-Formulas !!!
------------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
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:A15-A16>=0,A1:A15))
*** Both are Array-Formulas !!!
------------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
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
You are a lifesaver! I've been trying to figure this out for hours, until I came across your site.
What does the asterisk do in the formula? I know it's not multiplying anything, but I have never seen it this way.
=MIN(IF((Date_col=$C$3)*(Close_col0), Close_col, ""))
Mark McPherson,
Thank you!
The asterisk multiplies two arrays.
Example, (TRUE, TRUE, FALSE)*(TRUE, FALSE, FALSE) equals (1,0,0).
1 = TRUE
0 = FALSE
If there are repeated values in the close column, the expression returns the first occurrence of the value, regardless of the identified data range. how do we modify the formula to return the value in the date range identified?
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$7-B2, 0-B2))), ABS(IF(A2=$A$2:$A$7, $C$2:$C$7-B2, 0-B2)), 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$7-B2, 0-B2))), ABS(IF((A2=$A$2:$A$7)*(B2=$B$2:$B$7), $C$2:$C$7-B2, 0-B2)), 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 (2009-2017)
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 (2009-2017)
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 (2009-2017)
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!
its so heplful! thank you so much
Thanks, this one was giving me a headache!
U all are really amazing I am working as Demand Planner and all ur formulas really helpful to me.
Ravi
Thank you for commenting.
Thank you so much!
I have been doing my head in trying to find a way to do this (it seems like it would be a simple enough request but have still struggled until now). I've adapted your formula slightly to fit my purpose, but basically I had to be looking up max flood levels that occurred within a certain date range of a storm event, in my case: the date +-2 days. Instead of having the date range sitting separately, I nested it into my forumlas. This allows me to see the max height of a flood at multiple different flood gauges across s river system as the storm and flows pass through the system which can take days.
=MAXIFS($F$3:$F$40000,$E$3:$E$40000, "="&(AE34-2))
F = flood height (max values I need)
E = list of dates corresponding to the flood height
AE = specific date (the +- 2 searches 2 days before or after the event).
Thank you again.
Hello,
I need to do exactly this, excpet in the example you have the "close" column, but i have 48 columns to seek the MIN from.
I have 1 day per row data from May 2019 with 48 data points per row to the present day and i wish to extract the MIN.
I had been manualy naming a range then doing =MIN(named_range) but its tedious. Being able to do by date is much better but most solutions are hampered by a need for the range and data range to be the same size and shape.
Any tips?
Many thanks,
Stuart
=INDEX(B3:B12, MATCH(MIN(ABS(B3:B12-$E$2)), ABS(B3:B12-$E$2), 0))
This formula worked PERFECTLY for my needs. Thank you so much for sharing your knowledge on the topic.