Table of contents

  1. Match a date when a date range is entered in a single cell
  2. Match a date when a date range is entered in two cells
  3. Use VLOOKUP to search date in date ranges and return value on same row
  4. Match a date when date ranges sometimes overlap and return multiple results
  5. Functions in this post

Match a date when a date range is entered in a single cell

Column C contains the start and end date separated by a forward slash /. The formula in cell C9 splits the dates and checks if the date in cell C8 is in a date range, if it is it returns the adjacent value on the same row.

match date within date range

Array formula in C9:

=INDEX($B$3:$B$6, MIN(IF((DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8)*(DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8), MATCH(ROW($C$3:$C$6), ROW($C$3:$C$6)), "")))

How to create an array formula

  1. Select cell C9
  2. Click in formula bar
  3. Copy and paste array formula to formula bar
  4. Press and hold CTRL + SHIFT
  5. Press Enter
  6. Release all keys

Download excel file, see sheet Ex 1

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Match a date when a date range is entered in two cells

Array formula in cell C9:

=INDEX($B$3:$B$6, MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))))

How to enter an array formula

  1. Select cell C9
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining array formula in cell C9

Step 1 - Check if date is in any of the date ranges

($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6)

becomes

(39994>={39814;39904;39995;40087})*(39994<={39903;39994;40086;40178})

becomes

{TRUE;TRUE;FALSE;FALSE}*{FALSE;TRUE;TRUE;TRUE}

and returns

{0;1;0;0}

Step 2 - Return row number if a range is found

IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))

becomes

IF({0;1;0;0}, MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))

becomes

IF({0;1;0;0}, {1;2;3;4})

and returns {FALSE;2;FALSE;FALSE}

Step 3 - Return smallest value in array, ignore text and boolean values

MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))))

becomes

MIN({FALSE;2;FALSE;FALSE})

nad returns 2.

Step 4 - Return value from cell range

INDEX($B$3:$B$6, MIN(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6)))))

becomes

INDEX($B$3:$B$6, 2)

becomes

INDEX({"A";"B";"C";"D"}, 2)

and returns B in cell C9.

Download excel file, see sheet Ex 2

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Use VLOOKUP to search date in date ranges and return value on same row

The following formula uses only the VLOOKUP function, however the dates must be sorted ascending and if a date is outside date ranges 0 (zero) is returned. There can't be any overlapping date ranges and the formula can only return one value.

Formula in cell C13:

=VLOOKUP(C12,B3:C10,2,TRUE)

Download excel file, see sheet Ex 4

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Match a date when date ranges sometimes overlap and return multiple results

Array formula in cell C9:

=INDEX($B$3:$B$6, SMALL(IF(($C$8>=$C$3:$C$6)*($C$8<=$D$3:$D$6), MATCH(ROW($B$3:$B$6), ROW($B$3:$B$6))), ROW(A1)))

How to enter an array formula

  1. Select cell C9
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell C9
  2. Copy cell (not formula)
  3. Select cell range C10:C11
  4. Paste

Download excel file, see sheet Ex 3

matching-a-date-in-a-date-range v3.xlsx
(Excel 2007- Workbook *.xlsx)

Back to top

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

FIND(find_text,within_text,[start_num])
Returns the starting position of one text string within another text string. FIND is case-sensitive

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

LEFT(text,num_chars)
Returns the specified number of characters from the start of textstring

RIGHT(text,num_chars)
Returns the specified number of characters from the end of textstring

ROW(reference)
Returns the rownumber of a reference

LEN(text)
Returns the number of characters in a text string

DATEVALUE(date_text)
Converts a date in the form of text to a number that represents the date in Microsoft Office Excel date-time code

Back to top