I need help to find a formula that return value related within a given range of time.

Hi Gina,

You didn't enter your formula as array one

More about array formulas is here

https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

https://spreadsheeto.com/index-match/

https://www.youtube.com/watch?v=ontXHp9cwOQ

and in many other places.

If enter the formula with Ctrl+Shift+Enter it works correctly

Taking into account what your list of semesters is in chronological order that could be an alternative without using the array formula. Step by step

First, find the date of semester starts for which our date is within the range

=AGGREGATE(14,6,($C$3:$C$6)*($C$3:$C$6<=$D11)*($D$3:$D$6>=$D11),1)

(that is the largest date which meets criteria).

After that using MATCH we find on which place in the list that date is

=MATCH(K$11,$C$3:$C$6,0)

And combining all together with INDEX we find the name of Semester

=IFERROR(INDEX($B$3:$B$6,MATCH(AGGREGATE(14,6,($C$3:$C$6)*($C$3:$C$6<=$D11)*($D$3:$D$6>=$D11),1),$C$3:$C$6,0)),"out of project terms")

No array formula is required. I put above formula into the column G of the attached file.

Note: opening your file Excel says XML Scheme was corrupted and repaired the file (I used first attachment to your post), not what was the reason.