Looking for a formula to find max in a column based on condition in another

Gopikrishna Mamidipudi

I have Four columns

 

Task Number  Flag1      Flag2        Task name

10001             TRUE      TRUE           A

10002              TRUE      TRUE          B

10003              TRUE      TRUE          C

10004              FALSE      TRUE         D

10005              TRUE        TRUE        E

10006              FALSE      TRUE         F

10007              FALSE      FALSE       G

10008               FALSE     FALSE       H

 

I need to find the task name for the max task number that has FLAG1+FLAG2 as True. This indicates the last task completed on Project. Also, I need to find the next task that is identified by FLAG 1 being false and FLAG2 as being true.

 

In the above scenario 10005|E is the answer for the first. 10006|F is the answer for the second.

L_123

=max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2]