Looking for a value based on a date and value from 2 different cells.

[Tej92]

Hello everyone!
from the following table

Raised on	Action	Assigned date	Assigned to	completed
19/01/2023​	Extra equipment in the area	20/02/2023​	John Doe	06/02/2023​
29/01/2023​	No location for extra equipment	20/02/2023​	John Doe	open​

I'd like the cell to return the oldest action if under the completed column is still "open".
how can i do it?

Thanks in advance for the help!

 

[Tej92]

Good morning Peter, really appreciate the reply.
It works until I start adding another line, i added another line at the bottom with an older date in column A but it did not have open in column E, it still returned the new action line.
A little info on what I'm trying to achieve.
multiple users have another excel workbook, in that workbook they write the the action.
There is a submit button and once they click it, all the data regarding the action gets pasted in the above format in this workbook.
There is a summary sheets for all different users, in that summary sheet I'm trying to display the oldest action that is still open (that doesn't have a date in the column E, as if they complete the action there is another button that looks for the action and replaces "open" with the date they clicked the "complete" button).
Hope this makes it clearer.

thank you again for taking the time to follow this up, really appreciate it.

 

[Tej92]

Thank you Peter!! That's it!
It works perfectly.
really grateful for the help.
Have a Nice day.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.