Thread: Return name with largest value in multiple column array

Hi

I have a list of names in column A. In Column B to M there are listed scores in a competition.
Example: in column A I have the names Frank, William and Peter. In the columns behind these names (B to M) there are stated numbers as scores in a test. In this table I am able to see who has the best average score.
I addition I would like to have a top 5 list of these scores in a different table, and I am able to list the top 5 scores by adding a LARGE formula. What I am struggling to add in the top 5 table is who own this specific score. To be more specific I want to relate the LARGE value with the name in the A column.
I have tried to search for this, however I have only been able to find solutions with match and index formulas. These seems to not work for the LARGE formula with multiple columns.
Appreciate any help. Thank you.

See attachment.

If you names are in A2:A12 and respective averages are in F2:F12,

Then list your top 5 scores in say K2:K6 with formula:

=LARGE($F$2:$F$12,ROWS($A$2:$A2)) copied down 5 cells.

Then in L2 enter:

=INDEX($A$2:$A$12,SMALL(IF($F$2:$F$12=K2,ROW($A$2:$A$12)-MIN(ROW($A$2:$A$12))+1),COUNTIF(K$2:K2,K2)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Do you mean as per attached...

So score data is in B2:D12

in K2 for largest scores:

=LARGE($B$2:$D$12,ROWS($A$2:$A2))

copied down

in L2 for names:

=INDEX($A$2:$A$12,SMALL(IF($B$2:$D$12=K2,ROW($A$2:$A$12)-MIN(ROW($A$2:$A$12))+1),COUNTIF(K$2:K2,K2)))

confirmed with CTRL+SHIFT+ENTER and copied down.