Need help with a choose from a list formula

[afe5026]

Hello,
For a soccer league I'm making a statistics spreadsheet.

For a top scorer for each team stat, I have a list of all of the scorers and their clubs with the amount of goals scored.

Column A Column B Column C
Player # Goals Club


Is there any way to make a formula that will sort through the data and get the player from each club with the most goals and place it into another cell?

Thanks,
afe5026

sorry about formatting with the columns - can't fix it

[BadlySpelledBuoy]

Have a look at the link below. There are several ways of accomplishing a "MAXIF" (a function Excel sadly doesn't have) that you can adapt for your need.
It's hard to provide a more specific answer without seeing your workbook.

http://www.excelforum.com/tips-and-t...y-formula.html

BSB.

[daddylonglegs]

Here's one way to do that...

Assume you have a list of all clubs in E2 down, then in G2 use this formula for the largest number of goals scored by a player from that club

=MAX(IF(C$2:C$1000=E2,B$2:B$1000))

confirm with CTRL+SHIFT+ENTER

....and in F2

=INDEX(A$2:A$1000,MATCH(1,(B$2:B$1000=G2)*(C$2:C$1000=E2),0))

also confirmed with CTRL+SHIFT+ENTER

copy both formulas down the columns

Note: if there is a tie at any club this will pick only the first player listed for that club with the max number of goals