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!

 

[Peter_SSs]

I think I understand better now. Try this instead

23 03 07.xlsm
	A	B	C	D	E	F	G
1	Raised on	Action	Assigned date	Assigned to	Completed
2	19/01/2023	this is action 1	10/02/2023	Tej	6/02/2023		this is action 4
3	23/01/2023	this is action 2	16/02/2023	Joe	open
4	28/01/2023	this is action 3	11/03/2023	john	10/02/2023
5	20/01/2023	this is action 4	18/03/2023	Mark	open
6
Tej92 (2)

Cell Formulas
Range	Formula
G2	G2	=INDEX(SORT(FILTER(A2:B100,E2:E100="open"),1),1,2)

 

[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.