43 Responses to “Lookup the nearest date”

1. Aziz says:

   April 10, 2013 at 1:15 pm

   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

   Reply
   • Oscar says:

     April 16, 2013 at 1:01 pm

     Aziz,

     The formula works with blank datevalues.

     Thank you for commenting!

   • Andrew says:

     May 12, 2013 at 10:55 am

     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?

   • Oscar says:

     May 14, 2013 at 2:26 pm

     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))}

   • Jeremy says:

     September 12, 2013 at 8:40 pm

     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?

   • Oscar says:

     September 14, 2013 at 9:49 am

     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!

2. Kris says:

   November 2, 2013 at 9:00 pm

   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!

   Reply
3. Oscar says:

   November 8, 2013 at 3:37 pm

   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!

   Reply
4. Murtuza Vasanwala says:

   January 24, 2014 at 10:24 am

   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

   Reply
   • Oscar says:

     January 27, 2014 at 8:25 am

     Murtuza Vasanwala,

     I am sorry, I don´t understand. Can you describe in greater detail?

   • Murtuza vasanwala says:

     February 22, 2014 at 6:21 am

     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

   • Oscar says:

     February 25, 2014 at 2:27 pm

     Murtuza vasanwala,

     Read this:

     https://www.get-digital-help.com/2007/12/05/excel-find-closest-value/#criterion

5. Robert says:

   January 30, 2014 at 5:49 pm

   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.

   Reply
   • Oscar says:

     February 3, 2014 at 10:33 am

     Robert,

     =INDEX(A1:A10, MATCH(MIN(ABS(If(A1:A10>=E1,A1:A10-E1,""))), ABS(If(A1:A10>=E1,A1:A10-E1,"")), 0))

6. Marco says:

   September 16, 2014 at 11:45 pm

   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?

   Reply
7. Mike says:

   November 12, 2014 at 2:15 am

   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?

   Reply
8. Micky says:

   February 11, 2015 at 7:06 pm

   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

   Reply
   • Oscar says:

     February 11, 2015 at 8:03 pm

     Micky,

     thanks for sharing!

   • Jason says:

     December 2, 2015 at 8:15 pm

     This worked like a charm. Thanks

9. Mayetreyee Bishnu says:

   April 28, 2015 at 10:04 pm

   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

   Reply
   • Mayetreyee Bishnu says:

     April 28, 2015 at 10:15 pm

     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.

10. Micky says:

    April 29, 2015 at 12:40 am

    @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

    Reply
11. Micky says:

    April 29, 2015 at 12:43 am

    @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

    Reply
12. Seymour says:

    July 5, 2015 at 2:07 pm

    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

    Reply
13. Philipp says:

    July 28, 2015 at 10:08 pm

    Dear all,

    your advices are really helpful, I need the opposite of the comment (Jan, 30,2014) so smaller but closet to.

    Thanks

    Reply
14. Ehsan says:

    October 23, 2015 at 3:18 pm

    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

    Reply
15. Stephen says:

    October 29, 2016 at 12:02 am

    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

    Reply
    • Oscar says:

      October 31, 2016 at 9:46 am

      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))

    • Stephen says:

      October 31, 2016 at 8:57 pm

      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

    • Oscar says:

      October 31, 2016 at 9:29 pm

      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))

16. Michael (Micky) Avidan says:

    October 31, 2016 at 3:32 pm

    @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

    Reply
    • Oscar says:

      October 31, 2016 at 3:54 pm

      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.

17. Michael (Micky) Avidan says:

    October 31, 2016 at 4:38 pm

    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

    Reply
18. Stephen says:

    November 1, 2016 at 11:34 pm

    Thanks Oscar this has worked. I am taking my time to see how the formula works. It feels almost magical. thanks again

    Stephen

    Reply
19. Michael (Micky) Avidan says:

    November 2, 2016 at 12:38 am

    @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

    Reply
    • Oscar says:

      November 2, 2016 at 10:19 am

      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.

    • Oscar says:

      November 2, 2016 at 10:30 am

      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.

20. Tyler says:

    November 15, 2016 at 7:28 pm

    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

    Reply
    • Oscar says:

      November 17, 2016 at 9:05 am

      Tyler

      

      Array formula in cell C1:
      =MIN(IF(($A1<$F$2:$F$32)*($B1=$E$2:$E$32),$F$2:$F$32,""))

    • Tyler says:

      November 17, 2016 at 7:18 pm

      Thank you very much!

21. Ravi says:

    September 14, 2017 at 4:46 pm

    U all are really amazing I am working as Demand Planner and all ur formulas really helpful to me.

    Reply
    • Oscar says:

      September 15, 2017 at 2:18 pm

      Ravi

      Thank you for commenting.

22. No_Name_Analyst says:

    October 8, 2020 at 3:30 am

    =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.

    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 &lt; and > becomes &gt;

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.

Contact Oscar

You can contact me through this contact form