Criteria selection problem...

[JamesPrice]

Hi

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.

Many thanks

James

Can't seem to copy the pix so here goes:

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

[Tony Valko]

Try this...

Data Range

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

This array formula** entered in E2:

=INDEX(A2:A10,MATCH(MAX(IF(B2:B10=F2,C2:C10)),IF(B2:B10=F2,C2:C10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[JamesPrice]

Spot on Biff

Thanks for this

James

[Tony Valko]

You're welcome. Thanks for the feedback!