CODEWITHSUNDEEP
excel-formula
James
James

Reputation: 1

Criteria selection

I have a table the has Phase Name, Name, and Stage completion (which has dates and blanks)

In a separate part of the worksheet for each Name, I want to get the phase name which is based on the latest date in the Stage Completion. E.g A would be 'Closure' as the latest stage complete is 3 July, B would be Execution, and so on. I want to use a formula rather than the advanced Filter or dBase function. Is this possible? It thought I was half way there with MAX IF but got stuck.

      A             B         C
 1 PhaseName      Name Stage Completion
 2 Initiation      A        1/7
 3 Execution       A        2/7
 4 Closure         A        3/7
 5 Initiation      B        1/7
 6 Execution       B        4/7
 7 Closure         B 
 8 Initiation      C        1/7
 9 Execution       C 
 10 Closure        C

Upvotes: 0

Views: 15

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

You will want the following array formula:

=INDEX($A$2:$A$10,MATCH(MAX(IF($B$2:$B$10=F2,$C$2:$C$10)),IF($B$2:$B$10=F2,$C$2:$C$10),0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done properly Excel will put {} around the formula.

enter image description here

Upvotes: 0

Related Questions