Show value of row 1, where row 2 is greatest

Solved/Closed
Report
-
 Matt275 -
Hello,


0 1 2 3 4 5 6 7 8 9 10
0 2 5 6 5 1 3 1 0 0 0
1 4 2 4 5 4 2 1 0 0 0
0 4 6 5 1 5 0 1 0 0 1
1 0 5 8 5 3 1 0 0 0 0


in the table above the bold row is the number of goals in a game and below is the number of times this has occurred for each team. I'd like to be able to display the number of goals that occurs the most for each team.
For Example, for row 2, 3 goals occurs 6 times, hence the answer is 3. For row 3 the answer would be 4 as this has occurred 5 times.
Any help would be much appreciated

2 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,

Try and adapt this formula :

0 1 2 3 4 5 6 7 8 9 10		
0 2 5 6 5 1 3 1 0 0 0	=INDEX($A$1:$K$1,1,MATCH(LARGE(A2:K2,1),A2:K2,0))
1 4 2 4 5 4 2 1 0 0 0	=INDEX($A$1:$K$1,1,MATCH(LARGE(A3:K3,1),A3:K3,0))
0 4 6 5 1 5 0 1 0 0 1	=INDEX($A$1:$K$1,1,MATCH(LARGE(A4:K4,1),A4:K4,0))
1 0 5 8 5 3 1 0 0 0 0	=INDEX($A$1:$K$1,1,MATCH(LARGE(A5:K5,1),A5:K5,0))


Regards
Thanks very much for your help