Conditional Formula question

[Renee]

Good afternoon all,

I have 3 columns: Teacher Trainee Score

The teachers' names will repeat as each teacher will have multiple trainees.
I used advance filter to copy unique values of Teacher Names to a new
location in the workbook.

Next to each Teachers' name I would like to display the Trainee's name with
the maximum score among that Teacher's trainees.

Example
A B C
1 Teacher Trainee Score
2 Bob Sarah 100
3 Bob James 95
4 Michelle Harold 97
5 Michelle Lori 93

Teachers' Top performers!
8 Bob Sara
9 Michelle Harold

I have been experimenting with:
B8 would be =If(AND(C2:C5 = Max(C2:C5), A8 = A1:A5),B1:B5)

I would appreciate any guidance here!
Renee

 

[Bob Phillips]

=INDEX(B1:B20,SUMPRODUCT(--(C1:C20=MAX(IF(A1:A20=A2,C1:C20))),ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mrice]

I'd be tempted to use a user defined function here which scanned through
the teachers names and kept track of the maximum before reporting at the
end.

Martin

http://homepage.ntlworld.com/martin.rice1/

 

[Domenic]

Here's one possible solution where ties for top score are taken into
consideration. Also, it will allow you to easily display any amount of
top scores, such as Top 3, Top 5, Top 10, etc.

Assuming that A4:C15 contains the data, copy a unique list of teacher
names to Column E, starting at E4, then try the following...

F2: enter 1, indicating that you want a Top 1 list. If, for example,
you want a Top 3 list, enter 3 instead.

F4, copied down:

=LARGE(IF($A$4:$A$15=E4,$C$4:$C$15),$F$2)

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

G4, copied down:

=SUMPRODUCT(--($A$4:$A$15=E4),--($C$4:$C$15>=F4))

H4, copied across and down:

=IF(COLUMNS($H4:H4)<=$G4,INDEX($B$4:$B$15,MATCH(LARGE(IF($A$4:$A$15=$E4,$
C$4:$C$15-ROW($C$4:$C$15)/10^5),COLUMNS($H4:H4)),$C$4:$C$15-ROW($C$4:$C$1
5)/10^5,0)),"")

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

Hope this helps!