Sorry if I have completely butchered the terminology in the title. Relatively new to Excel.

I have the following table.

Name	Score A	Score B	Score C
Bob	10	8	6
Sue	9	12	10
Joe	11	2	4
Susan	7	9	10
Tim	10	12	4
Ellie	9	8	7

What I am trying to achieve is that for each person, to return the score type for that person's best score. I'm referencing the person's name on another sheet.

For example. For Susan;

Their best score is 10 and that is under Score C.

So I want the final value in the Score Type column in my other sheet for Susan to be Score C

Like so

Name	Best Score	Score Type
Ellie	9	Score A
Bob	10	Score A
Susan	10	Score C

I know to get the index of each persons row by

=MATCH(A2,$A$2:$A, 0)

I can get the max value for that person via

{=MAX(IF($A$2:A = A2,($B$2:$D)))}

I'm just not sure how to use that information to return the column label corresponding to the person and their max score.

Any help would be greatly appreciated

CodePudding user response：

I think it is pretty easier. Try-

=INDEX($B$1:$D$1,MATCH(MAX(B2:D2),B2:D2,0))

For dynamic spill array-

=BYROW(B2:D7,LAMBDA(x,FILTER(B1:D1,x=MAX(x))))

EDIT: Then try below formula-

=INDEX($B$1:$D$1,1,MAX(($A$2:$A$7=$I3)*($B$2:$D$7=$J3)*(COLUMN($B$2:$D$7)))-COLUMN($A$2))

CodePudding user response：

For Larger Data you could use this one