Determine the lowest value in a range of cells where the order is termined by an second range of cells

[BVOPP]

my excel sheet contains a lot of tasks (rows), in one column the status is maintained like (dropdowlist):
"", Open, Started, Closed etc...

One activity consists of a lot of these tasks, so i want to determine the lowest status of the task and assign that to the task.

For example

task 1 Open
task 2 Started
task 3 Closed
task 4 Started
Activity = Open

task 1 Started
task 2 Started
task 3 Closed
task 4 Started
Activity = Started

etc....

How can i simpliest do that in an xlsx. ???

 

[Jeffrey Mahoney]

You should be able to do it with this formula. Just change the ranges to match your needs.

=IF(IFERROR(MATCH("open",D13:D37,0),0)>0,"Open",IF(IFERROR(MATCH("started",D13:D37,0),0)>0,"Started",IF(IFERROR(MATCH("closed",D13:D37,0),0)>0,"Closed","")))