Get top3 of a list with names and teams, top3 of each team with name of highest scorer?

[behedwin]

Is it possible to do this?

I have this sheet

Team	NAMES	SCORE
T1	JOHAN	1
T1	ANNA	23
T1	MARIA	3
T2	BENJAMIN	5
T3	OLOF	55
T1	LARS	16
T3	LISA	12
T3	OSKAR	5

<tbody>
</tbody>

Id like to get the top 3 highest scores. of each team.


I want to present the result like this (for each color)


Team T1

name	score

<tbody>
</tbody>

Read something about LARGE function
And i got that to work to find the highest score but i cant filter out just the highest score in Team T1
And also i cant figure out how to get the name of the person that have the highest score.

 

[ExcelElliott]

	A	B	C	D	E	F
1	Team	NAMES	SCORE
2	T1	JOHAN	1
3	T1	ANNA	23
4	T1	MARIA	3
5	T2	BENJAMIN	5
6	T3	OLOF	55
7	T1	LARS	16
8	T3	LISA	12
9	T3	OSKAR	5
10
11	Team T1				Team T3
12	Name	Score			Name	Score
13	ANNA	23			OLOF	55
14	LARS	16			LISA	12
15	MARIA	3			OSKAR	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A13)) B14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A14)) B15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A15)) F13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13)) F14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14)) F15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A13 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B13,$A$2:$A$9&$C$2:$C$9,0))} A14 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B14,$A$2:$A$9&$C$2:$C$9,0))} A15 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B15,$A$2:$A$9&$C$2:$C$9,0))} E13 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F13,$A$2:$A$9&$C$2:$C$9,0))} E14 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F14,$A$2:$A$9&$C$2:$C$9,0))} E15 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F15,$A$2:$A$9&$C$2:$C$9,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself Apologies for the array formula but it was necessary to ensure that if the same score is achieved by two players on different teams, the correct player is identified. May be a better way to handle it.

<tbody>
</tbody>

 

[behedwin]

	A	B	C	D	E	F
1	Team	NAMES	SCORE
2	T1	JOHAN	1
3	T1	ANNA	23
4	T1	MARIA	3
5	T2	BENJAMIN	5
6	T3	OLOF	55
7	T1	LARS	16
8	T3	LISA	12
9	T3	OSKAR	5
10
11	Team T1				Team T3
12	Name	Score			Name	Score
13	ANNA	23			OLOF	55
14	LARS	16			LISA	12
15	MARIA	3			OSKAR	5

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A13)) B14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A14)) B15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A15)) F13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13)) F14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14)) F15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A13 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B13,$A$2:$A$9&$C$2:$C$9,0))} A14 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B14,$A$2:$A$9&$C$2:$C$9,0))} A15 {=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B15,$A$2:$A$9&$C$2:$C$9,0))} E13 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F13,$A$2:$A$9&$C$2:$C$9,0))} E14 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F14,$A$2:$A$9&$C$2:$C$9,0))} E15 {=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F15,$A$2:$A$9&$C$2:$C$9,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself Apologies for the array formula but it was necessary to ensure that if the same score is achieved by two players on different teams, the correct player is identified. May be a better way to handle it.

<tbody>
</tbody>

Thank you.
But i have a bit of a problem implementing this to my large sheet

Sheet1 = where i have my raw data
Sheet2 = where i want to display this toplist.

=AGGREGATE(14;7;'Sheet1'!$F$2:$F$3674/('Sheet1'!$A$2:$A$3674=RIGHT(I2;2));ROWS(I$9:I9))

Column F sheet1 = scores
Column A sheet1 = team names
Column I is in sheet2 where this formula is and in cell I2 i have the team name im looking for
The toplist starts at I9
Text in I2 = T1 and in sheet1 the team column all teams starts with T1 or T2 or T3 or T4 etc. (then after the two first letters it can be anything... like T1 Lions etc.)

 

[behedwin]

Ok i got it to work with the first formula

I did some changes to it, but it seems to work fine
=AGGREGATE(14;7;'Sheet1'!$F$2:$F$3674/('Sheet1'!$A$2:$A$3674="T1");ROWS(I$9:I9))

As you can see i now state the team name in the formula instead from a cell, i am fine with this.
But i can not get the array to work atall.. i think this "RIGHT" formula in the array is messing things up and i cant get rid of it and state the team name in the formula

Can you help?

This is my attempt
=INDEX('Sheet1'!$B$2:$B$3674;MATCH(RIGHT($I$2;2)&$I9;'Sheet1'!$A$2:$A$3674&'Sheet1'!$F$2:$F$3674;0))

Sheet 1 B column = names
Sheet 1 A column = teams
Sheet 1 F column = scores

Cell I2 = where i write the team name, but id rather have that in the formula like the other formula solution.
Cell I9 = where toplist begins, top1 is in cell I9

But this one does not work, i get N/A as result....
I do end it with ctrl+shift+enter to close it

 

[ExcelElliott]

Sorry about that, after researching I discovered a better solution, see below.

Hope this helps.

	A	B	C	D	E	F
1	Team	NAMES	SCORE
2	T1	JOHAN	1
3	T1	ANNA	16
4	T1	MARIA	12
5	T2	BENJAMIN	5
6	T3	OLOF	10
7	T1	LARS	13
8	T3	LISA	12
9	T3	OSKAR	5
10
11	Team T1				Team T3
12	Name	Score			Name	Score
13	ANNA	16			LISA	12
14	LARS	13			OLOF	10
15	MARIA	12			OSKAR	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A13 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B13)),COUNTIF($B$13:B13,B13))) B13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B13)) A14 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B14)),COUNTIF($B$13:B14,B14))) B14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B14)) A15 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B15)),COUNTIF($B$13:B15,B15))) B15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B15)) E13 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F13)),COUNTIF($F$13:F13,F13))) F13 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13)) E14 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F14)),COUNTIF($F$13:F14,F14))) F14 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14)) E15 =INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F15)),COUNTIF($F$13:F15,F15))) F15 =AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To NAMES =Sheet1!$B$2:$B$9 SCORE =Sheet1!$C$2:$C$9 Team =Sheet1!$A$2:$A$9 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[behedwin]

Thank you, i got it to work now.
Very nice!