Specifying the person who has achieved the highest result

[Mary Ann]

I have a list of people in one column, say A1:A10 with their results in the
next column, say B1:B10. I have calculated the highest result using the MAX
function, located in B13.

I want to show the name of the person who achieved the highest score next to
the MAX function – in my example this would be in A13.

I have been researching Array formulas, which I have some understanding of,
and would have thought I could have used one to give me the result I want in
combination with the IF function.

However, I cannot make it work.

How do I do this? I am a competent user but do not do programming.

I am using 2003.

 

[Bernie Deitrick]

Mary Ann,

In A13, use the formula

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,FALSE))

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One way:

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,FALSE))

 

[Domenic]

Try...

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

Note that if there is more than one person with the highest result, the
formula will only return the first occurrence. Are you concerned about
ties?

 

[Mary Ann]

Thanks for this. Yes, I would like to see all the names of the people who’s
results match the maximum score. How would I achieve that?

 

[Mary Ann]

Thanks for this

Mary Ann,

In A13, use the formula

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,FALSE))

HTH,
Bernie
MS Excel MVP

 

[Mary Ann]

Thanks for your reply

One way:

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,FALSE))

 

[Domenic]

Here's a formula system, courtesy of Aladin Akyurek...

Assumptions:

A2:A10 contains the name

B2:B10 contains the result

Formulas:

C2, copied down

=RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1

D1: enter 1 (indicating that you want a 'Top 1' list)

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2:C10))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROW()-ROW(F$2)+1<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(ROW()-ROW(F$2)+1,$C$
2:$C$10,0)),"")

G2, copied down:

=IF(F2<>"",INDEX(B$2:B$10,MATCH(ROW()-ROW(G$2)+1,$C$2:$C$10,0)),"")

Note that you can easily change this from a 'Top 1' list to a 'Top 5'
list (or other Top N list) by entering 5 in D1.

Hope this helps!

 

[Mary Ann]

Wow - some formula! I'll have a go! Thanks

Here's a formula system, courtesy of Aladin Akyurek...

Assumptions:

A2:A10 contains the name

B2:B10 contains the result

Formulas:

C2, copied down

=RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1

D1: enter 1 (indicating that you want a 'Top 1' list)

E1:

=MAX(IF(B2:B10=INDEX(B2:B10,MATCH(D1,C2:C10,0)),C2:C10))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

F2, copied down:

=IF(ROW()-ROW(F$2)+1<=$D$1+$E$1,INDEX(A$2:A$10,MATCH(ROW()-ROW(F$2)+1,$C$
2:$C$10,0)),"")

G2, copied down:

=IF(F2<>"",INDEX(B$2:B$10,MATCH(ROW()-ROW(G$2)+1,$C$2:$C$10,0)),"")

Note that you can easily change this from a 'Top 1' list to a 'Top 5'
list (or other Top N list) by entering 5 in D1.

Hope this helps!