Return the Highest score based on House and return the user details

[garylin]

Hi Excel Experts,
I was wondering if you could help me work out something with my sheet below:

Book4
	A	B	C	D	E	F
1	UID	Preferred	Surname	Year Level	House	Score
2	137565	James	ca	7	Boold	10
3	137518	Jessica	va	7	Crayne	10
4	137819	Jadon	ds	7	Boold	10
5	137684	Brett	acer	7	Boold	3
6	137706	Bob	rap	7	Crayne	5
7	137807	Bailey	er	7	Starlock	10
8	137786	Charlie	sdr	7	Crayne	2
9	137531	Charles	fds	7	Boold	1
10	138221	Tim	Row	7	Boold	8
11	137575	Tina	daf	7	McAurey	10
12	137791	Sophia	gat	7	Crayne	9
13	137745	Olivia	stag	7	Starlock	9
Sheet1

I am trying to get the Person with the Highest Score based on which House they belong to.
So For House Crayne, I want it to return
137518 Jessica va 10

Then same for the top scorers of each of the other houses.

I am currently using the formula: {=INDEX(A2:A$13,MATCH(MAX(IF((E$2:$E$13="Crayne"),F2:F13)),F2:F13,0))}
However no matter which house I change the name to, it keeps giving the same data.

Book4
	J	K	L	M
6	137565	James	ca	10
7	137565	James	ca	10
Sheet1

Cell Formulas
Range	Formula
J6	J6	=INDEX(A2:A$13,MATCH(MAX(IF((E$2:$E$13="Boold"),F2:F13)),F2:F13,0))
K6	K6	=INDEX(B2:B$13,MATCH(MAX(IF((E$2:$E$13="Boold"),F2:F13)),F2:F13,0))
L6	L6	=INDEX(C2:C$13,MATCH(MAX(IF((E$2:$E$13="Boold"),F2:F13)),F2:F13,0))
M6	M6	=INDEX(F2:F$13,MATCH(MAX(IF((E$2:$E$13="Boold"),F2:F13)),F2:F13,0))
J7	J7	=INDEX(A2:A$13,MATCH(MAX(IF((E$2:$E$13="Crayne"),F2:F13)),F2:F13,0))
K7	K7	=INDEX(B2:B$13,MATCH(MAX(IF((E$2:$E$13="Crayne"),F2:F13)),F2:F13,0))
L7	L7	=INDEX(C2:C$13,MATCH(MAX(IF((E$2:$E$13="Crayne"),F2:F13)),F2:F13,0))
M7	M7	=INDEX(F2:F$13,MATCH(MAX(IF((E$2:$E$13="Crayne"),F2:F13)),F2:F13,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Also I am unsure of how to distinguish it if there are two people with the same score.

Any help is greatly appreciated!

 

[bebo021999]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	UID	Preferred	Surname	Year Level	House	Score				UID	Preferred	Surname	Year Level	House	Score
2	137565	James	ca	7	Boold	10				137565	James	ca	7	Boold	10
3	137518	Jessica	va	7	Crayne	10				137518	Jessica	va	7	Crayne	10
4	137819	Jadon	ds	7	Boold	10				137807	Bailey	er	7	Starlock	10
5	137684	Brett	acer	7	Boold	3				137575	Tina	daf	7	McAurey	10
6	137706	Bob	rap	7	Crayne	5
7	137807	Bailey	er	7	Starlock	10
8	137786	Charlie	sdr	7	Crayne	2
9	137531	Charles	fds	7	Boold	1
10	138221	Tim	Row	7	Boold	8
11	137575	Tina	daf	7	McAurey	10
12	137791	Sophia	gat	7	Crayne	9
13	137745	Olivia	stag	7	Starlock	9
Sheet2

Cell Formulas
Range	Formula
J2:M13	J2	=IFERROR(INDEX(A$2:A$13,MATCH(1,INDEX(($E$2:$E$13=$N2)*($F$2:$F$13=$O2),),0)),"")
N2:N13	N2	=IFERROR(INDEX($E$2:$E$13,MATCH(0,INDEX(COUNTIF($N$1:N1,$E$2:$E$13),),0)),"")
O2:O13	O2	=IFERROR(AGGREGATE(14,6,$F$2:$F$13/($E$2:$E$13=$N2),1),"")

 

[garylin]

Hi bebo021999,

Thank you very much for your reply! That is exactly what I was after
Was there any way that it could possibly flag when there are people with the same score?

Many Thanks for your help!

 

[bebo021999]

What kind of flag do you like? Highlight with back color or somewhat?

 

[Peter_SSs]

What about this (for 365)?

22 03 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	UID	Preferred	Surname	Year Level	House	Score		UID	Preferred	Surname	Year Level	House	Score
2	137565	James	ca	7	Boold	10		137565	James	ca	7	Boold	10
3	137518	Jessica	va	7	Crayne	10		137518	Jessica	va	7	Crayne	10
4	137819	Jadon	ds	7	Boold	10		137819	Jadon	ds	7	Boold	10
5	137684	Brett	acer	7	Boold	3		137807	Bailey	er	7	Starlock	10
6	137706	Bob	rap	7	Crayne	5		137575	Tina	daf	7	McAurey	10
7	137807	Bailey	er	7	Starlock	10
8	137786	Charlie	sdr	7	Crayne	2
9	137531	Charles	fds	7	Boold	1
10	138221	Tim	Row	7	Boold	8
11	137575	Tina	daf	7	McAurey	10
12	137791	Sophia	gat	7	Crayne	9
13	137745	Olivia	stag	7	Starlock	9
Higest

Cell Formulas
Range	Formula
H2:M6	H2	=FILTER(A2:F13,F$2:F$13=MAXIFS(F$2:F$13,E$2:E$13,E$2:E$13))
Dynamic array formulas.

 

[garylin]

What kind of flag do you like? Highlight with back color or somewhat?

Hi bebo021999,

Yeah a Highlight alerting the user would be good, followed by a display like what Peter has would be good too.

Many Thanks,

 

[Peter_SSs]

If you want a solution without vba then you could consider Conditional Formatting like this

22 03 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	UID	Preferred	Surname	Year Level	House	Score		UID	Preferred	Surname	Year Level	House	Score
2	137565	James	ca	7	Boold	10		137565	James	ca	7	Boold	10
3	137518	Jessica	va	7	Crayne	10		137518	Jessica	va	7	Crayne	10
4	137819	Jadon	ds	7	Boold	10		137819	Jadon	ds	7	Boold	10
5	137684	Brett	acer	7	Boold	3		137807	Bailey	er	7	Starlock	10
6	137706	Bob	rap	7	Crayne	5		137575	Tina	daf	7	McAurey	10
7	137807	Bailey	er	7	Starlock	10
8	137786	Charlie	sdr	7	Crayne	2
9	137531	Charles	fds	7	Boold	1
10	138221	Tim	Row	7	Boold	8
11	137575	Tina	daf	7	McAurey	10
12	137791	Sophia	gat	7	Crayne	9
13	137745	Olivia	stag	7	Starlock	9
Higest (2)

Cell Formulas
Range	Formula
H2:M6	H2	=FILTER(A2:F13,F$2:F$13=MAXIFS(F$2:F$13,E$2:E$13,E$2:E$13))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:M13	Expression	=COUNTIF($L$2:$L$13,$L2)>1	text	NO

.. or add another column to the original data & change the FILTER formula slightly.

22 03 21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	UID	Preferred	Surname	Year Level	House	Score	Dupe Max		UID	Preferred	Surname	Year Level	House	Score	Dupe Max
2	137565	James	ca	7	Boold	10	**		137565	James	ca	7	Boold	10	**
3	137518	Jessica	va	7	Crayne	10			137518	Jessica	va	7	Crayne	10
4	137819	Jadon	ds	7	Boold	10	**		137819	Jadon	ds	7	Boold	10	**
5	137684	Brett	acer	7	Boold	3			137807	Bailey	er	7	Starlock	10
6	137706	Bob	rap	7	Crayne	5			137575	Tina	daf	7	McAurey	10
7	137807	Bailey	er	7	Starlock	10
8	137786	Charlie	sdr	7	Crayne	2
9	137531	Charles	fds	7	Boold	1
10	138221	Tim	Row	7	Boold	8
11	137575	Tina	daf	7	McAurey	10
12	137791	Sophia	gat	7	Crayne	9
13	137745	Olivia	stag	7	Starlock	9
Higest (3)

Cell Formulas
Range	Formula
I2:O6	I2	=FILTER(A2:G13,F$2:F$13=MAXIFS(F$2:F$13,E$2:E$13,E$2:E$13))
G2:G13	G2	=IF(AND(COUNTIFS(E$2:E$13,E2,F$2:F$13,F2)>1,F2=MAXIFS(F$2:F$13,E$2:E$13,E2)),"**","")
Dynamic array formulas.

 

[garylin]

Hi Peter,

Thanks very much for your reply, that first solution looks great!
Many thanks for both of your help with this!

Many Thanks,

 

[Peter_SSs]

You're welcome. Glad we could help.