Return Column Header of first entry in range meeting a criteria

[esmithuk]

I have been working on this for about a day now, searched everywhere and cannot seem to find a solution

I need to return the column header of the earliest entry which meets a particular Phase in column A, i.e. for Phase 3 I would expect formula to return May-19.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Phase	Description	Activity ID	01-Jan	01-Feb	01-Mar	01-Apr	01-May	01-Jun	01-Jul
2	1				100
3	2					100
4	1				100
5	1				100
6	2					100	100
7	3							100
8	1				100
9	3							100	100
10	3									100
11
12
13				01-Feb
14				01-Feb
Sheet1

I already have these 2 formulas which both return the earliest entry without Phase:

In D13:
{=INDEX(D1:J1,SMALL(IF((D2:J10<>0),COLUMN(D1:J1)-COLUMN(D1)+1,""),1))}

In D14:
{=INDEX($D$1:$J$1,MATCH(MIN(IF($D$2:$J$10<>"",COLUMN($D$1:$J$1)-1)),COLUMN($D$1:$J$1)-1,0))}

They both return Feb-19, but I need the result to be May-19, the earliest entry for Phase 3. As soon as I include multiple criteria in the match part, by looking at all entries that equal 3 in column A i get error.

What do need to include to consider the entries in column A?

 

[kweaver]

A solution (I always look for a solution then try to improve on it) is to use a helper column.
I assumed you'd run till December, so I put a helper column P2:P10

=IF($A$17=A2,MATCH(FALSE,ISBLANK(D2:O2),0),"")

This is an array formula so it needs CTRL+Shift+Enter in P2 and filled down thereafter.

Then, in A17 I put the Phase (e.g., 3) and in B17 (formatted for the date style you want):

=INDEX(D1:O1,SMALL(P2:P10,1))

HTH

 

[esmithuk]

Thanks kweaver

Yes I'm aware I can use a helper column, or even a helper row .... I am trying to avoid this strategy.

Cheers