find oldest date based on multiple criteria

Hi All,

I need to find the oldest date in a column based on some criteria.
Attached file has example data and in yellow the required output based on the data in columns A:D.
Data in A:D is expanded daily by adding similar rows at the bottom of the list, so I can't work with a fixed range.

Criteria:
1. If follow-up date (Column C) is empty or has a date in the past, take the oldest date in column B
2. If follow-up date isn't empty, but has a date in the future (date > today), disregard this line

I need a formula in the yellow fields which will return the oldest date based on above 2 criteria, matching the Teams (column A versus H1, H6 and H9) and the report date (column D versus I1, I6 and I9)

Hopefully I've explained the above in a clearly manner, if not, just shout ;-)

Thanks for all the effort from your side!
Peter
oldest date.xlsx

Hi Marvin,

Thanks for the above, but it doesn't give me the correct output, as this doesn't take into account the follow-up date criteria. So the oldest dates in the PT are not matching the required output as shown in the yellow fields.

Is there any way to incorporate this?

Thanks,
Peter