Find the nth best of a list based on multiple criteria

[NeedInformation]

Trying to figure out the proper index(match()) to find the nth best of a list based on a couple of factors.

For example, if I have an unsorted table with rows of classes with class sizes and average grade in that class. I'd like to find the nth best highest average. Ties will be determine the the largest sized class. Ties beyond that sorted alphabetically.

Using the table below the top 3 classes should return:

1. Phys.Ed (Highest Grade and no classes with this grade nor class size)
2. SocialMedia (Next highest grade (95) and the largest class size with that grade)
3. Band (Next highest grade (95) and although it has the same class size as French (which comes earlier in the list, it is alphabetically first))

I know I can use the large function to find the nth largest but it's the multiple criteria that is stumping me. Should be same as ordering the table by (Grade Average, then Students, then Class name).

Thank you, in advance, for your help!

Class Name (guaranteed unique)	Students	Grade Average
History	20	75
Algebra	22	80
French	15	95
SocialStudies	22	87
Phys Ed	18	100
Social Media	32	95
Art History	20	75
Government	23	60
Band	15	90

 

[Fluff]

How about

++Fluff.xlsm
	A	B	C	D	E	F	G
1	Class Name (guaranteed unique)	Students	Grade Average
2	History	20	75		Phys Ed	18	100
3	Algebra	22	80		Social Media	32	95
4	French	15	95		Band	15	95
5	SocialStudies	22	87
6	Phys Ed	18	100
7	Social Media	32	95
8	Art History	20	75
9	Government	23	60
10	Band	15	95
11
12
Sheet4

Cell Formulas
Range	Formula
E2:G4	E2	=INDEX(SORT(A2:C10,{3,2,1},{-1,-1,1}),{1;2;3},{1,2,3})
Dynamic array formulas.

 

[NeedInformation]

Thank you for the quick reply!

If the table were to have hundreds of rows what would be the most concise way of writing out the formula? Assume I'm looking of for the top N where N could be 1 to 10?

Finally, I neglected to add that I'm selecting classes from a specific campus. I tried to edit my post and ran into a timeout issue.

The criteria would be to only look at those classes from given campus. You could add a column D to my prior example and entitle it campus. All could be from the same campus (Say alpha) for the example to work correctly. Assuming Band was at a different campus (beta) then the result of looking for the top 3 from the alpha campus would be:

PhysEd
Social media
French

 

[Fluff]

How about

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Class Name (guaranteed unique)	Students	Grade Average
2	History	20	75	A		A	Phys Ed	18	100
3	Algebra	22	80	A		4	Social Media	32	95
4	French	15	95	A			French	15	95
5	SocialStudies	22	87	A			SocialStudies	22	87
6	Phys Ed	18	100	A
7	Social Media	32	95	A
8	Art History	20	75	A
9	Government	23	60	A
10	Band	15	95	B
11
12
Sheet4

Cell Formulas
Range	Formula
G2:I5	G2	=INDEX(SORT(FILTER(A2:D100,D2:D100=F2),{3,2,1},{-1,-1,1}),SEQUENCE(F3),{1,2,3})
Dynamic array formulas.

 

[NeedInformation]

Works perfectly and, exactly, what I need. I need to learn about dynamic arrays!

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.