Retrieve value based on date range - Excel file automation/formula

I am looking for someone to help me create a formula or automate an Excel file so that I am able to retrieve a value based on a date range. The type of data I'm looking to retrieve is text. I will require this to happen for an Excel file. With the given criteria and parameters, I'm hoping to receive a value as the output from this project. The project should be able to calculate a value within an Excel sheet and I would like to have easy access to the result.

find attached
-instructions
-excel file if sample data and expected result