find-nearest-date

Array formula in E3:

=INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0))

Recommended articles:

Find date range

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

  1. Select cell E3
  2. Type or copy/paste above array formula to formula bar
    formula bar
  3. Press and hold Ctrl + Shift
  4. 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:

How to use Excel’s 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 1-17-2009 in cell E3.

Learn more about the INDEX function:

INDEX function explained

Fetch a value in a data set based on coordinates.

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