INDEX MATCH Find Largest Date with criteria

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
376
Office Version
  1. 2016
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.

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
deleted, again
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,623
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try
Code:
=INDEX($O$1:$Y$1,MATCH(MAX(O10:Q10,X10:Y10),O10:Y10,0))
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
376
Office Version
  1. 2016
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
Code:
=INDEX($O$1:$Y$1,MATCH(MAX(O10:Q10,X10:Y10),O10:Y10,0))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,236
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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)))))
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,623
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try
Code:
=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

Well-known Member
Joined
Dec 3, 2018
Messages
13,236
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
13,236
Office Version
  1. 2007
Platform
  1. Windows
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))))))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,236
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top