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

B

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
Is it possible to do this?

I have this sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Team
[/TD]
[TD]NAMES
[/TD]
[TD]SCORE
[/TD]
[/TR]
[TR]
[TD]T1
[/TD]
[TD]JOHAN
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]T1
[/TD]
[TD]ANNA
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]T1
[/TD]
[TD]MARIA
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]T2
[/TD]
[TD]BENJAMIN
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]T3
[/TD]
[TD]OLOF
[/TD]
[TD]55
[/TD]
[/TR]
[TR]
[TD]T1
[/TD]
[TD]LARS
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]T3
[/TD]
[TD]LISA
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]T3
[/TD]
[TD]OSKAR
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]name
[/TD]
[TD]score
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
ABCDEF
ANNAOLOF
LARSLISA
MARIAOSKAR

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NAMES[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]SCORE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JOHAN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ANNA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MARIA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BENJAMIN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OLOF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]LARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]LISA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OSKAR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team T1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team T3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Score[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]55[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A13))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A14))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A15))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B13,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B14,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B15,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F13,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F14,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F15,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
</tbody>[/TABLE]
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. [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
ExcelElliott said:
ABCDEF
TeamNAMESSCORE
T1JOHAN
T1ANNA
T1MARIA
T2BENJAMIN
T3OLOF
T1LARS
T3LISA
T3OSKAR
Team T1Team T3
NameScoreNameScore
ANNAOLOF
LARSLISA
MARIAOSKAR

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]55[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A13))[/TD]
[/TR]
[TR]
[TH]B14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A14))[/TD]
[/TR]
[TR]
[TH]B15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(A$13:A15))[/TD]
[/TR]
[TR]
[TH]F13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13))[/TD]
[/TR]
[TR]
[TH]F14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14))[/TD]
[/TR]
[TR]
[TH]F15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B13,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH]A14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B14,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH]A15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($A$11,2)&$B15,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH]E13[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F13,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH]E14[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F14,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
[TR]
[TH]E15[/TH]
[TD="align: left"]{=INDEX($B$2:$B$9,MATCH(RIGHT($E$11,2)&$F15,$A$2:$A$9&$C$2:$C$9,0))}[/TD]
[/TR]
</tbody>[/TABLE]
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.[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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.)
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
Sorry about that, after researching I discovered a better solution, see below.

Hope this helps.


ABCDEF
ANNALISA
LARSOLOF
MARIAOSKAR

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]NAMES[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]SCORE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]JOHAN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]ANNA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MARIA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BENJAMIN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OLOF[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]LARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]LISA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]T3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]OSKAR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team T1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Team T3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Score[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A13[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B13)),COUNTIF($B$13:B13,B13)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B13))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A14[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B14)),COUNTIF($B$13:B14,B14)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B14))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A15[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($A$11,2))*(SCORE=$B15)),COUNTIF($B$13:B15,B15)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($A$11,2)),ROWS(B$13:B15))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E13[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F13)),COUNTIF($F$13:F13,F13)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F13[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F13))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F14)),COUNTIF($F$13:F14,F14)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F14[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F14))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=INDEX(NAMES,AGGREGATE(15,6,(ROW(NAMES)-ROW($B$2)+1)/((Team=RIGHT($E$11,2))*(SCORE=$F15)),COUNTIF($F$13:F15,F15)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=AGGREGATE(14,7,$C$2:$C$9/($A$2:$A$9=RIGHT($E$11,2)),ROWS(F$13:F15))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]NAMES[/TH]
[TD="align: left"]=Sheet1!$B$2:$B$9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]SCORE[/TH]
[TD="align: left"]=Sheet1!$C$2:$C$9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Team[/TH]
[TD="align: left"]=Sheet1!$A$2:$A$9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
136
dreid1011
dreid1011
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
553
StephenCrump
S
T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
147
Peter_SSs
Peter_SSs
V
  • Solved
Sports competition top scorer list (round by round)
2
Replies
17
Views
244
Kaper
Kaper
F
  • Question Question
Scoresheet creation for a trivia game
Replies
1
Views
331
Rijnsent
Rijnsent

Forum statistics

Threads
1,223,704
Messages
6,173,984
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top