Find the top Scores per School

[Guest]

I'm looking for help on finding a formula to express the nth largest score
for a particular school.

Examine the following:

Sheet 1:

School Name Math_Score Verbal_Score
Washington Tom 81 71
Madison Jim 79 66
Lincoln Bill 90 88
Washington Zack 81 84
Washington Suzy 71 79
Lincoln Robby 59 64
Lincoln Tracy 71 77
Madison Jennifer 88 88
Madison Emily 84 79
Washington Earl 70 72
Lincoln Claire 92 91
Washington Kate 88 93
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 fx(?)=88 fx(?)=93
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'd like to stay away from filters and 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]

One play which provides a possibility to get the desired results ..

Assume source in Sheet1, cols A to D, data from row2 down

In Sheet2,

Create a DV droplist in A1 to select the Schools
Click Data > Validation,
Allow: List,
Source: Washington,Madison,Lincoln

Labels in B2:C2 are: Math_Score, Verbal_Score
Labels in A3:A5 are: 1st, 2nd, 3rd

Put in D3:
=IF(OR($A$1="",Sheet1!$A2="",Sheet1!C2=""),"",IF(Sheet1!$A2=$A$1,Sheet1!C2-ROW()/10^10,""))
Copy D3 to E3, fill down to cover the max expected extent of data in Sheet1,
eg down to E100. Leave D1:E2 blank. Hide away cols D & E.

Then just place in B3:
=IF(ROW(A1)>COUNT(D),"",INDEX(Sheet1!C:C,MATCH(LARGE(D,ROW(A1)),D,0)-1))
Copy B3 to C3, fill down to C5. This returns the required "best 3" scores
results for the school selected in A1 (duplicates will be covered). Test it
out by changing the selection in A1 to another school. You can simply extend
the fill-down to C6, C7, etc if required to extract out further the
descending sort for the Math & Verbal scores.

---