Nth largest values with criteria

[Tashat]

Hi everyone

I have a table of data and scores. I want to have another table which returns the top three highest scores and the names of the people with the highest score. I am almost there with it, but I want to return the top three scores for people who are labelled as CEM (column B) and the top three scores for people labelled as CA (Column B). I haven't been able to manage that last part. I also want my formula to be able to manage tied scores. e.g. Janice and Richard have both scored 7, but my formula repeats Janice's name and I want it to show Janice and Richard as 2nd and 3rd. I'm using Excel 365 and I'd appreciate anyone's help. Thank you.

Xlookup mr excel.xlsx
	A	B	C	D	E	F	G	H
1	Name	Type	Score				CEM
2	Amy	CEM	1			1	Charlotte
3	Geoff	CA	5			2	Janice
4	Dave	CA	2			3	Janice
5	Al	CEM	2
6	Sarah	CEM	3
7	Charlotte	CEM	8				CA
8	Natasha	CEM	5			1
9	Janice	CA	7			2
10	Martin	CEM	5			3
11	Rachel	CA	1
12	Louisa	CA	5
13	Sandra	CEM	4
14	Danielle	CEM	4
15	Richard	CEM	7
16
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX(Names,MATCH(LARGE(Score,F2),Score,0))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$2:$C$15	G2:G4
Names	=Sheet1!$A$2:$A$15	G2:G4
Score	=Sheet1!$C$2:$C$15	G2:G4

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Name	Type	Score				CEM
2	Amy	CEM	1			1	Charlotte
3	Geoff	CA	5			2	Richard
4	Dave	CA	2			3	Natasha
5	Al	CEM	2
6	Sarah	CEM	3
7	Charlotte	CEM	8				CA
8	Natasha	CEM	5			1	Janice
9	Janice	CA	7			2	Geoff
10	Martin	CEM	5			3	Louisa
11	Rachel	CA	1
12	Louisa	CA	5
13	Sandra	CEM	4
14	Danielle	CEM	4
15	Richard	CEM	7
16
Master

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX(SORTBY(FILTER(A2:A15,B2:B15=G1),FILTER(C2:C15,B2:B15=G1),-1),SEQUENCE(3))
G8:G10	G8	=INDEX(SORTBY(FILTER(A2:A15,B2:B15=G7),FILTER(C2:C15,B2:B15=G7),-1),SEQUENCE(3))
Dynamic array formulas.

 

[Tashat]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Name	Type	Score				CEM
2	Amy	CEM	1			1	Charlotte
3	Geoff	CA	5			2	Richard
4	Dave	CA	2			3	Natasha
5	Al	CEM	2
6	Sarah	CEM	3
7	Charlotte	CEM	8				CA
8	Natasha	CEM	5			1	Janice
9	Janice	CA	7			2	Geoff
10	Martin	CEM	5			3	Louisa
11	Rachel	CA	1
12	Louisa	CA	5
13	Sandra	CEM	4
14	Danielle	CEM	4
15	Richard	CEM	7
16
Master

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX(SORTBY(FILTER(A2:A15,B2:B15=G1),FILTER(C2:C15,B2:B15=G1),-1),SEQUENCE(3))
G8:G10	G8	=INDEX(SORTBY(FILTER(A2:A15,B2:B15=G7),FILTER(C2:C15,B2:B15=G7),-1),SEQUENCE(3))
Dynamic array formulas.

That's perfect as always! Thank you so much. You're an Excel genius Fluff

 

[Fluff]

You're welcome & thanks for the feedback.