return the largest value associated with a duplicate occurrence

[Guest]

I'm looking for help on finding a formula to express the largest (or 2nd
largest,etc) score for a particular school.

Examine the following:

Sheet 1:

Col_A Col_B Col_C Col_D
1 School Name Math_Score Verbal_Score
2 Washington Tom 81 71
3 Madison Jim 79 66
4 Lincoln Bill 90 88
5 Washington Zack 81 84
6 Washington Suzy 71 79
7 Lincoln Robby 59 64
8 Lincoln Tracy 71 77
9 Madison Jennifer 88 88
10Madison Emily 84 79
11 Washington Earl 70 72
12 Lincoln Claire 92 91
13 Washington Kate 88 93
14 Madison Jeff 86 85

On Sheet 2 I wish to show the best 3 scores from each school as:

Washington
Math_Score Verbal_Score
1st (?) (?)
2nd (?) (?)
3rd (?) (?)

Lincoln
Math_Score Verbal_Score
1st (?) (?)
2nd (?) (?)
3rd (?) (?)

Madison
Math_Score Verbal_Score
1st (?) (?)
2nd (?) (?)
3rd (?) (?)
______________________

I was attempting to use the "Looking up a particular occurrence" portion at
the bottom of this page as a reference:

http://www.bettersolutions.com/excel/EDH113/MU516714331.htm

But this example only gives me associated value for the 2ND occurrence of
the duplicate value. I'm looking for the Largest occurrence of a duplicate
value - in my case, the given school.

PARAMETERS:
Sheet1 cannot be edited. I simply need to leave every other part of the
workbook as-is and create formulas for the (?) cells to produce the desired
results.

Many thanks in advance for your help on this puzzle.

 

[Guest]

Assuming
School in A2:A400
Math in C2:C400
Your criteria in F1 (example: Washington)

=SUMPRODUCT(LARGE(($A$2:$A$400=$F$1)*($C$2:$C$400),ROWS($1:1)))

copy down as far as needed

 

[JE McGimpsey]

One way:

NOTE: these formulae must be array-entered (CTRL-SHIFT-ENTER or
CMD-RETURN)

For Washington's math scores:

=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,1)
=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,2)
=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,2)

Similarly, for Madison's verbal scores:

=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,1)
=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,2)
=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,3)

 

[Guest]

Works perfectly! Thanks!

One way:

NOTE: these formulae must be array-entered (CTRL-SHIFT-ENTER or
CMD-RETURN)

For Washington's math scores:

=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,1)
=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,2)
=LARGE((Sheet1!A2:A14="Washington")*Sheet1!C2:C14,2)

Similarly, for Madison's verbal scores:

=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,1)
=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,2)
=LARGE((Sheet1!A2:A14="Madison")*Sheet1!D214,3)