Question: I have my example setup like the image below:

![enter image description here](https://i.stack.imgur.com/viwXF.png)

I'm trying to make a formula in cell F3 that will:

*"Select a random EVENT between A2:A11 where the USER INPUT DATE (E3) is between the START(B2:B11) and FINISH(C2:C11) dates*

The only thing I can think of is:

```
=COUNTIFS(C2:C11,">="&E3,B2:B11,"<="&E3)

```

But that only returns the number of EVENTS that are possible to choose from. 

Any input would be appreciated

AN EXAMPLE OF A FINAL VALUE FOR THIS WOULD BE EITHER:

Event 3 or Event 4 because 7/2/2012 occurs between the start and finish dates of both events

Answer: 
I came up with this formula:

```
=INDEX(A:A,SMALL(IF((E3>=B2:B11)*(E3<=C2:C11),ROW(A2:A11)),
                  RANDBETWEEN(1,SUMPRODUCT((E3>=B2:B11)*(E3<=C2:C11)))
                )
      )

```

with array entry (`CTRL`+`SHIFT`+`ENTER`).

1. `IF((E3>=B2:B11)*(E3<=C2:C11),ROW(A2:A11))` returns array of row numbers where `E3` falls between `START` and `FINISH` dates
2. `RANDBETWEEN(1,SUMPRODUCT((E3>=B2:B11)*(E3<=C2:C11)))` generates rand number between `1` and count of appropriate events
3. `SMALL(IF(..),RANDBETWEEN(..))` takes random row number from array from step 1.
4. `INDEX(A:A,SMALL(..))` gets corresponding event name.
I know this has been answered, but I wanted to provide a slightly different option that doesn't require array entry (Ctrl+Shift+Enter):

```
=IFERROR(INDEX(A:A,LARGE(INDEX((B2:B11<=E3)*(C2:C11>=E3)*ROW(A2:A11),),RANDBETWEEN(1,COUNTIFS(B2:B11,"<="&E3,C2:C11,">="&E3)))),"No events found on that date")

```

And here is a backwards compatible version of the formula:

```
=IF(SUMPRODUCT(--(B2:B11<=E3),--(C2:C11>=E3))=0,"No events found on that date",INDEX(A:A,LARGE(INDEX((B2:B11<=E3)*(C2:C11>=E3)*ROW(A2:A11),),INT(RAND()*SUMPRODUCT(--(B2:B11<=E3),--(C2:C11>=E3)))+1)))

```