INDEX MATCH Find Largest Date with criteria

[surkdidat]

Please can someone help advise where I have gone wrong with this.

In Column N is where this formula is and I want to generate the name of the headed column in O, P, Q, X or Y only (R through to W should not be included in the results)

I want it to find the "largest" (most recent) date, and then that to display the column header.

So in column O is Pipeline, Column P is WIP, Column Q is Testing, Column X is Waiting Promotion, Column Y is Completed, Column Z is Cancelled. These are all date fields, and will be populated as per the progress of each work item.

At the moment, it is looking for the last field to have an entry, (this includes Columns R to W) and not necessarily the most recent date.

=IFERROR(INDEX($O$1:$Z$1,MATCH(LARGE((O10:Q10,X10:Z10),1),O10:Z10)),"")

 

[AlanY]

deleted, again

 

[Scott T]

Try

=INDEX($O$1:$Y$1,MATCH(MAX(O10:Q10,X10:Y10),O10:Y10,0))

 

[surkdidat]

Thank you - the only slight issue I have is if there is the same date in columns P and Q for example, I want it to take the most right hand column with the date in please?

Try

=INDEX($O$1:$Y$1,MATCH(MAX(O10:Q10,X10:Y10),O10:Y10,0))

 

[DanteAmor]

Try this:

=INDEX(A1:Z1,1,IF(SUMPRODUCT((O2:Q2=MAX(O2:Q2,X2:Z2))*(COLUMN(O1:Q1)))=0,SUMPRODUCT((X2:Z2=MAX(O2:Q2,X2:Z2))*(COLUMN(X1:Z1))),SUMPRODUCT((O2:Q2=MAX(O2:Q2,X2:Z2))*(COLUMN(O1:Q1)))))

 

[Scott T]

Try

=INDEX($O$1:$Y$1,LARGE(IF(MAX(O10:Q10,X10:Y10)=O10:Y10,COLUMN(O10:Y10)-COLUMN(O10)+1),1))

This is an array formula and must be committed with CTRL+SHIFT+ENTER not just ENTER. If done correctly you will see {} around the formula in the formula bar.

 

[DanteAmor]

Edit:

=INDEX(A1:Z1,1,IF(SUMPRODUCT((O2:Q2=MAX(O2:Q2,X2:Z2))*(COLUMN(O1:Q1)))=0,SUMPRODUCT(MAX((X2:Z2=MAX(O2:Q2,X2:Z2))*(COLUMN(X1:Z1)))),SUMPRODUCT(MAX((O2:Q2=MAX(O2:Q2,X2:Z2))*(COLUMN(O1:Q1)))))) <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Edit:

Please consider the following:

=INDEX(A1:Z1,1,MAX(SUMPRODUCT(MAX((X2:Z2=MAX(O2:Q2,X2:Z2))*(COLUMN(X1:Z1)))),SUMPRODUCT(MAX((O2:Q2=MAX(O2:Q2,X2:Z2))*(COLUMN(O1:Q1))))))

 

[surkdidat]

Thankyou all - issue resolved

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.