# INDEX MATCH Find Largest Date with criteria

#### surkdidat

##### Active Member
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.

deleted, again

Last edited:

#### Scott T

##### Well-known Member
Try
Code:
``=INDEX(\$O\$1:\$Y\$1,MATCH(MAX(O10:Q10,X10:Y10),O10:Y10,0))``

#### surkdidat

##### Active Member
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

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
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

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
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

##### Active Member
Thankyou all - issue resolved

Replies
6
Views
200
Replies
1
Views
47
Replies
8
Views
103
Replies
12
Views
90
Replies
0
Views
68

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?

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