Returning Column Index/Value Based on Max value of Specific row

mcclosa 0
Viewed 42

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

excel-formula vlookup excel
2 Answers

I think it is pretty easier. Try-

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

enter image description here

For dynamic spill array-

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

enter image description here

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))

enter image description here

Harun24hr0

For Larger Data you could use this one

Index Match

WIL0
Related