## 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:

Elizabeth asks: I need to figure out the date range of a cell. So if cell "E2" has a date […]

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 E4
- Type or copy/paste above array formula to formula bar

- Press and hold Ctrl + Shift
- Press Enter

Learn more about array formulas:

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 click on "Evaluate formula".

Click "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:

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:

Gets a value in a specific cell range based on a row and column number.

### Download excel example file

find-nearest-date.xls

(Excel 97-2003 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 […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Converts negative numbers to positive numbers, in other words, the ABS function removes the sign. Excel Function Syntax ABS(number) Arguments […]

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 1/11/2009-1/17/2009 […]

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 […]

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 […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

### 42 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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

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

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!

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

Ravi

Thank you for commenting.