CODEWITHSUNDEEP
excelexcel-formulamax
Aziz Ghazi
Aziz Ghazi

Reputation: 11

Return the heading of the maximum value for more than one Max

I have five columns in Excel and I want to return the maximum value's column heading name. However, there are cases where the max values are repeated more than once for the same row. So, I am trying to return both column names.

The green values are the min and red are the max. In row 4, it is clearly there is more than one Max with same value, I would like to return B and E in the stream cell.

I tried this formula in Excel using the index: 

=IF(ISNUMBER(A6),INDEX($B$5:$F$5,1,MATCH(L6,B6:F6,0)),"")

Excel Screen Shot

Upvotes: 0

Views: 579

Answers (2)

bkraines
bkraines

Reputation: 83

You can use FILTER() to return multiple values. In this example, I've concatenated them with TEXTJOIN():

enter image description here

In cell E2 enter the formula =MAX(A2:D2). In cell F2 enter =TEXTJOIN(,,FILTER(A$1:D$1,A2:D2=E2)). Copy down.

Upvotes: 0

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

MATCH returns the first match, so I think you need something gross like the following (I started with the first column and row, but you can shift the column numbers -- the rows are designed to be draggable)

=IF(ISNUMBER(F2),IF(F2=A2,$A$1,"")&IF(F2=B2,$B$1,"")&IF(F2=C2,$C$1,"")&IF(F2=D2,$D$1,"")&IF(F2=E2,$E$1,""))

Upvotes: 0

Related Questions