Find Max value and return Date

[msquared99]

I have some dates in C1:N1 and data in C2:N4500.

I have the max value of each row of data in column O2:O4500.

I would like to in column P return the corresponding date of the max value from row 1.


I have this formula in column P to get the cell address of the max value.
=CELL("address",INDEX(C2:N2,MATCH(MAX(C2:N2),C2:N2,0)))

In column Q I have =MID(P2,2,1) to extract the column letter.

In column R I have =INDIRECT(Q2&1) to return the date in row 1.

How can I combine all this to get what I need?

Thanks.

 

[Chihiro]

Can you upload sample file? It will be much easier to get/give help.

Edit: And here comes Deepak to the rescue

 

[Deepak]

msquared99

You was closely there!

Here's couple ways for the same.

It's already having you just did minor optimization.
=INDEX($C$1:$N$1,1,MATCH(MAX(C2:N2),C2:N2,0))

Another!!

=SUMIFS($C$1:$N$1,C2:N2,MAX(C2:N2))
=SUMPRODUCT($C$1:$N$1*(C2:N2=MAX(C2:N2)))


You may also use Large function to play with it.

 

[Deepak]

One more!

=LOOKUP(2,1/(MAX(C2:N2)=C2:N2),$C$1:$N$1)

cons of all approach!

Index will show first corresponding date if max value having more than one cells in the row.

Lookup will show last corresponding date if max value having more than one cells in the row.

SUMIF & SUMPRODUCT is only suitable when you confirmed that values are identical in each row means there's no duplication of the max value in that range.

 

[msquared99]

Deepak,

I never thought of the SUMIFS. I like that option, it is simple and clean.

Thanks a bunch!