Excel – Display column header containing highest value in a spreadsheet

microsoft excelspreadsheetworksheet-function

So, I have a list of names and values, linked here:

Spreadsheet

Ideally, I want to locate the highest value in the spreadsheet, and then display the name of the person with the highest value overall.

So far, in, say, the 'Tuesday' row, I've been able to select the highest value, and getting the name 'Frank', who has the highest value, using this formula…

=INDEX($B$1:$M$1,MATCH(MAX(B4:M4),B4:M4,0))

…and then changing it accordingly for each other 'day'.

What I want to do is select the name of the person with the absolute highest value over all of the 'days'.

In the attached example, it would be "Joseph" with a value of "589".

I know that I can use MAX(B2:M8) to get the highest value of "589" displayed in a cell, which is perfect, but now I want to display the actual name of that person who got the highest value, which in this example would be "Joseph".

How would I go about doing that?

Any assistance would be appreciated. Thank you.

Best Answer

1] In B1:M1 housed the person name and B2:M8 housed of your value

2] Name of person who got the highest value of the days

In O2, enter formula :

=INDEX(B1:M1,SUMPRODUCT((B2:M8=MAX(B2:M8))*COLUMN(A1:L1)))

enter image description here

Edit #.1

If the max value have duplicate, then formula changed to >>

In O2, enter array formula (Shift+Ctrl+Enter)

=TEXTJOIN(", ",1,INDEX(B1:M1,N(IF(1,AGGREGATE(15,6,COLUMN(A1:L1)/(B2:M8=MAX(B2:M8)),ROW(INDIRECT("1:"&COUNTIF(B2:M8,MAX(B2:M8)))))))))

enter image description here

Related Question