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. Match a date when date ranges sometimes overlap and return multiple results

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

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 v2.xls
(Excel 97-2003 Workbook *.xls)

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

Download excel file, see sheet Ex 2

matching-a-date-in-a-date-range v2.xls
(Excel 97-2003 Workbook *.xls)

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 v2.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

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

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

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