Get Max and Corresponding Text

[Courtney]

Hi all,

I have a table that looks something like this:

A B C D
x 50 150 15
y 5 50 15
z 50 4
total 100 5 200 34

I need to be able to find the Max of the total column, and get the
corresponding title (A,B,C,or D) of that maximum number. I then need
to get the 2nd, 3rd, and 4th highest totals, along with their
corresponding heading (A, B, C, D). Can someone tell me which function
I can achieve this with?

Thanks,
C

 

[Pete_UK]

I put your headings in row 1 starting with B1, and put x y and z in
A2:A4. The numbers then went in B2:E4, with the totals on row 5. Then
you can use these formulae in the cells stated:

A8: =LARGE(B$5:E$5,ROW(1:1))

B8: =INDEX(B$1:E$1,MATCH(A8,B$5:E$5,0))

Then copy these into rows 9:11.

Hope this helps.

Pete

 

[Courtney]

I put your headings in row 1 starting with B1, and put x y and z in
A2:A4. The numbers then went in B2:E4, with the totals on row 5. Then
you can use these formulae in the cells stated:

A8:    =LARGE(B$5:E$5,ROW(1:1))

B8:    =INDEX(B$1:E$1,MATCH(A8,B$5:E$5,0))

Then copy these into rows 9:11.

Hope this helps.

Pete

That works wonderfully. Thank you!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete