match date within date range

Formula in C9:

=INDEX(B3:B6, SUMPRODUCT(--(DATEVALUE(LEFT(C3:C6, FIND("/", C3:C6)-1))<=C8), --(DATEVALUE(RIGHT(C3:C6, LEN(C3:C6)-FIND("/", C3:C6)))>=C8), ROW(C3:C6)-MIN(ROW(C3:C6))+1)) + ENTER

Download excel file for this tutorial.

matching a date in a date range.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