VLOOKUP and LARGE (MAX?) - Finding the largest value in each group of data....

Hi everyone,
I am stuck with something I managed to sort out in the past, however I can't seem to find the example file I used in the past so I thought it's probably easiest to post this here.
Let me give you an example of my data:

Peter - 5 - 8.3
Frank - 4 - 7.4
Susan - 5 - 8.1
Betty - 3 - 6.9
Chris - 3 - 7.1
Claudia - 4 - 8.1

Now, imagine the first column being the name of the player, the second column the team the player is in, and the third column the score.
What I want is to find the name with the highest score in each team, so the output would be:

group 5: Peter
group 4: Claudia
group 3: Chris

As said, I had figured this out in the past with an array formula with LARGE and VLOOKUP, as I actually want to look up the top three players in a list that's a little longer than my example, so I actually need:

Rank1 group5: ...
Rank2 group5: ...
Rank3 group5: ....

Rank1 group4: ...
Rank2 group4: ...
Rank3 group4: ....

Can you help me???
Thanks so much,

Titus

Just to add, please don't tell me to sort the data, I am looking for a formula...

Does this help
http://www.exceltip.com/st/Find_the_...Excel/382.html

If that link doesn't help

Try this array formula (confirmed with Ctrl+Shift+Enter)
=INDEX(A2:A7,MATCH(MAX(IF(B2:B7=5,C2:C7)),C2:C7,0))

change the 5 to a reference that results in the team #

Yes, thanks, that did the trick!
Basically I just changed the formula to use LARGE instead of MAX, so I can have my top 3 players...

=LARGE((Sheet2!A2:A400=A1)*(Sheet2!B2:B400),1)

A1 contains the team of the player (i.e. team 2), column B contains the scores. Now I can just do a VLOOKUP. My only problem is if two players in one team have the exact same score...
How does this RANK function work; is that what I need?

You need to combine the RANK() function with a COUNTIF() function.
Rank deals with ties by skipping ranks. If 2 players tie for top score then there will be no rank value of 2 - instead there will be two 1's and then a 3.
The COUNTIF() checks for that.
See this link for a detailed description: http://www.cpearson.com/excel/Rank.aspx

Btw, a pivot table could also do the trick.

Create a pivot twble with the data and sort in ascemding order :-)

Originally Posted by daddylonglegs

Note that this doesn't always work, it may return a player from a team other than 5 (if the max score for team 5 is also the score for a player in another team, and that score appears first), you need to use a formula like this:

=INDEX(A2:A7,MATCH(1,(C2:C7=MAX(IF(B2:B7=5,C2:C7)))*(B2:B7=5),0))

Sorry it to me a little longer to reply as I was on the road. Just to let everyone that this works perfectly! Thanks a lot for your help, I am sure this can help others that have similar needs in the future, too!

Thanks again,

Titus.