Help with VLookup and dates

[brooksc29]

I'm trying to get this highlighted cell

to lookup the data matching in this column

and if the date falls between these dates in this table, return the Text to the right of where it falls between

this is the formula I am using, but it's not working

=INDEX((TextRange, SUMPRODUCT((VALUE(LEFT(DateDateRange, FIND(":", DateDateRange)-1))<datetolookup)*(value(right(datedaterange, len(datedaterange)-find(="" :="" ,="" datedaterange)))="">DateToLookup)*(ROW(TextRange)-ROW($c$2))),0)

please help!</datetolookup)*(value(right(datedaterange,>

 

[brooksc29]

I changed the formula to a VLookup instead of DateToLookup and still not working...
INDEX((TextRange, SUMPRODUCT((VALUE(LEFT(DateDateRange, FIND(":", DateDateRange)-1))<VLOOKUP(B6, PITCHERSplatSTATS, 11, FALSE)*(VALUE(RIGHT(DateDateRange, LEN(DateDateRange)-FIND(":", DateDateRange)))>VLOOKUP(B6, PITCHERSplatSTATS, 11, FALSE)*(ROW(TextRange)-ROW($c$2))),0)