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

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

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

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

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

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