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.

Mary Ann,

In A13, use the formula

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

HTH,
Bernie
MS Excel MVP


"Mary Ann" wrote in message
...
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.

One way:

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


In article ,
"Mary Ann" wrote:

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.

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?

In article ,
"Mary Ann" wrote:

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.

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

"Domenic" wrote:

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?

In article ,
"Mary Ann" wrote:

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.

Thanks for this

"Bernie Deitrick" wrote:

Mary Ann,

In A13, use the formula

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

HTH,
Bernie
MS Excel MVP


"Mary Ann" wrote in message
...
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.

Thanks for your reply

"JE McGimpsey" wrote:

One way:

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


In article ,
"Mary Ann" wrote:

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.

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!

In article ,
"Mary Ann" wrote:

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

"Domenic" wrote:

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?

In article ,
"Mary Ann" wrote:

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.

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

"Domenic" wrote:

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!

In article ,
"Mary Ann" wrote:

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?

"Domenic" wrote:

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?

In article ,
"Mary Ann" wrote:

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.