0

I have an excel file with 3 columns corresponding to Team name, Player name, and score. I would like to extract the top 5 players based on score in each team. I found a solution when there's not a grouping by team involved Solution for top 5.

I tried to add an "if" statement to filter for teams (like "if(TEAM_COLUMN=TEAM_NAMES_CONSTANTS, SCORE_COLUMN, 0), but it doesn't seem to work.

=INDEX($B$2:$B$28,MATCH(1,INDEX(($A$2:$A$28=LARGE($A$2:$A$28,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$28)=0),),0))
Community
  • 1
  • 1
Chris
  • 1
  • 1
  • 1

1 Answers1

1

I would suggest creating a PivotTable with the Team Name and Player Name in ROWS and Score in Values, then use the Value Filter "Top 10..." (which can be changed to any integer) to display the 'top scorers'. You could also add Team Name to FILTERS to view each Team separately.

Holy Name Scribes
  • 29
  • 6
  • I was hoping specifically to get a formula to populate a cell area. There are over 40 teams, and I set up VBA code that generates a sheet for each team, with the sheets all having formulas to grab data automatically from a source file. I just have a problem wrapping my mind around this particular formula for some reason. This youtube video https://www.youtube.com/watch?v=rKDI-kdBsjY is very similar to what I want (plus the team criteria, of course) – Chris Jun 14 '15 at 02:59