Replace dates with text

I'm trying to get this highlighted cell

\1

to lookup the data matching in this column

\1

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

\1

this is my formula in the first picture highlighted cell. It's not working but I feel like it's close. PLEASE HELP!!

=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)

can you please upload a sample file instead of pictures? And please use the forum facilities to upload files. Then everyone here can actually access them.