Complicated lookup and evaluation...

OK, I've got a good one for you!
This is data on capture of Carolina chickadees.
So the "code" field in the excel file below is an identifier of where one was captured, that is the stationID~netID~period (same as in column C)~year(same as in column C). Column G, "match?" is true if there is a station and net run in that period in the next year.

CHICKADEE.xlsx

I have another sheet that has the same "code" and the start/end times for each net. There are (or can be) multiple records for each code in that sheet. What I want to do is, if Match? is TRUE, see if the capture time (column F) falls between the start/end times for the subsequent year, same parameters otherwise. Thus, the column E value should be greater than a start time and less than an end time in the next year. (This is a survey effort validation step.) So, something like...

for each row
if column G value (match?) = true
{ row.columnFvalue (time)
generate subsequent year identifier, which would be year+1 and the station/pd info like the "code" column
search for the records in the other sheet that have that subsequent year identifier
is start time < row.columnFvalue (time) < end time for any records? #maybe stop statement/loop once true
put true or false value in next empty column (H) in Chickadee excel sheet}

Can anyone help me get started? Anything is appreciated! Thank you!!

Maybe something like in the added sheet?

Thank you for the reply!

Here is my sheet, updated with my interpretation of your formula and a few changes...
CHICKADEE.xlsx

Notice here, I went ahead and calculated the subsequent year, so now I am matching column G to the following sheet. Also, I included the other sheet. However, it does not give the expected output

To rephrase for any others reading this thread, if the "match?" column is true, I want to match the value in column G to the next sheet "recent" column B (which can have multiple records with that value). If the time (column H) on the first sheet falls within the range (C and D on the next sheet) of any of the matching records, that last column should be TRUE.