I am trying to work out the following problem where:

Cell A1 = Date

Column B= Event Name

Column C= Start Date

Column D = End date

Cell E1 = answer

I am trying to write a formula without macros where the Date in cell A1 can be found within the range of column C and Column D and return the corresponding B (event name or names) in E1

A simple if statement below works for 1 cell in B, C & D but not when trying to search the entire column.  When I try to do that I get a Value error

Let's say that the data in columns B, C and D are in rows 2 to 80.

Enter the following array formula, confirmed with Ctrl+Shift+Enter, in E1:

=INDEX(B2:B80, MATCH(1, (C2:C80<=A1)*(D2:D80>=A1), 0))

This works if there is a single match. For multiple matches, I'd use VBA.

Excel 2010
With multiple matches.
No VBA macro.
http://www.mediafire.com/view/tiaataggwm8v50i/04_06_15.xlsx