In Excel, how do I get the header corresponding to the max value from a subset of a range?

Question

I'm pushing beyond my Excel knowledge here. I'm trying to do a poll like thing in Excel. My problem lies on showing the selected result. Here's what I have so far:

I need to select the header corresponding to the cell with the highest value in the range B2:G2 (type 1). However, if there's a tie, I need to select the header corresponding to the highest value in the range B3:G3 amongst the cells with highest values in the range B2:G2.

In my sample, column "bb" and "cc" both share highest value on type 1 (5). So, in order to determine the winner, I need to compare the highest value for type 2 between them. Since "bb" is 0 and "cc" is 1, I expect "cc" as final result.

Components for formula are below:

J2: Displays the count of cells on line 2 with the highest value in the range. So, 2. I did that with COUNTIF comparing with MAX.

K2: Displays the first header it finds with the highest value on line 2. I managed with the following formula:

=INDEX($B$1:$G$1;0;MATCH(MAX($B$2:$G$2);$B$2:$G$2;0))

To be honest, I don't fully understand that formula. Did it with help of tutorials from the internet.

I2: Displays "TIE" when there's a tie on range B2:G2. Otherwise display the winning header (K2).

J3: Displays the number of cells with the maximum value on range B3:G3 but only considering winning cells from line 2. I did that with COUNTIFS.

=COUNTIFS(B3:G3;LARGE(B3:G3;1);B2:G2;MAX(B2:G2))

Edit: Just found out by entering number "4" on B3 that this formula above is also not working...

I3: Should follow the same pattern as the cell above. Displays "TIE" when there's still a TIE. Otherwise would display winning header (to be presented on K3).

K3: I don't know what to put here. Probably because I don't quite understand that formula with INDEX, MATCH and so on, I can't figure out a way to check the highest value between the two "winning" columns from the line above and get the header.

Could somebody help me with this?

Answer 1

Here is an approach with sumproducts. I dont really inderstand what results you want in I3, J3, and K3. will try to workout.

I2:

=IF(SUMPRODUCT(--(B2:G2=MAX(B2:G2)))>1,"TIE","")

J2:

=SUMPRODUCT(--(B2:G2=MAX(B2:G2)))

K2:

=IF(B7>1,OFFSET(B1,0,SUMPRODUCT(--(B3:G3=MAX(B3:G3))*--(B2:G2=MAX(B2:G2))*{0,1,2,3,4,5})),OFFSET(B1,0,SUMPRODUCT(--(B2:G2=MAX(B2:G2))*{0,1,2,3,4,5})))

the {0,1,2,3,4,5} refers to the number of headers, if there are more, this array needs to changed

Answer 2

First, let's establish if there is a tie. As you have discovered, you can do this by counting how many times the highest number appears in the range.

=COUNTIF($B2:$G2;MAX($B2:$G2))

If that count is more than 1, then there is a tie.

=IF(COUNTIF($B2:$G2;MAX($B2:$G2))>1;"TIE";"no tie")

In case of a tie you want to involve the values in row 3 as a tie breaker. We could add them to the values in row 2 using this array formula. You must confirm the array formula with Ctrl+Shift+Enter, not just Enter, otherwise it won't work.

=INDEX($B$1:$G$1,MATCH(MAX(((IF(B2:G2=MAX(B2:G2),MAX(B2:G2),0))+B3:G3)),INDEX((B2:G2+B3:G3),0)))

You only want to factor in row 3 if there is a tie, though, so you can re-use the IF statement from above and replace the "tie" in the formula above with the array formula and remember to press Ctrl+Shift+Enter!!

=IF(COUNTIF($B$2:$G$2,MAX($B$2:$G$2))>1,INDEX($B$1:$G$1,MATCH(MAX(((IF(B2:G2=MAX(B2:G2),MAX(B2:G2),0))+B3:G3)),INDEX((B2:G2+B3:G3),0))),"no tie")

You already have the formula to look up the value if there is no tie.

My system uses the comma as the list separator. I have manually replaced these with semicolons in the formulas I posted, but please bear with me if I may have missed one.

Now you can copy these formulas down to row 3. If there is a tie in the data in row 3, you will need data in row 4 to break the tie.

To understand the Index/Match combo, start with your first formula and read it from the inside out. The Max() finds the largest number. The Match() returns the position, i.e. column number, of the largest number in the range B2 to G2, i.e. 2 (the second column in the range). Index looks at B1 to G1 and returns the column value from the position that the Match returned, i.e. the 2nd column, which is the text bb.

Using row 3 as the tie breaker, the formula works pretty much the same, only that rows 2 and 3 are added together when the value in row 2 is the Max value and then that number is used to find the Max and the Match.