Good day,

I have been struggling with this formula for ages and have finally given up. What I would like to do is to do a lookup on the concatenated values of Province, Department and Initiative No, which has multiple values in the status of milestone column, and then give the answer based on a condition.

The condition for this example should be, that if the returned status / or statuses of the initiative is all 1, then the value should be 1, if it's all 2, then the value should be 2, and so on.....but if the values returned from the status of the initiative is a combination of 1,2 or 3, then it should give me the answer 2. If 4 is part of the combination of values returned, then the value should be 4.

Zero - 0 should be excluded from the formula as it is part of planned values and not actual.

I hope this makes sense.

Thanks!
Data.xlsx