Pull top 3 values and Row Headings

[vguida]

Players	AtBats	Batting Average
Mike	20	.325
Dave	22	.133
Vince	18	.210
Nick	25	.305
Alex	21	.266

I want to pull the Top 3 batter and their batting average. I can use the max/large functions, but cant get the index or offset to work.

Thanks,

[NBVC]

Assuming your data, with headers, is in Sheet1, A1:C6.

Now, assuming the results are going to be in Sheet2, starting in A2... and also assuming you can have duplicate AtBats, you will need a way to pull the unique names and averages...

So first use the LARGE() function to pull the top scores.

In A2 of Sheet2:

=LARGE(Sheet1!$B$2:$B$6,ROWS(Sheet1!$A$2:$A2))

copied down to get the number of scores you want.

Then to get the Player names in B2:

=INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COUNTIF(A$2:A2,A2)))

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copy down.

Similar for Batting Averages, replacing the INDEXed range.

[vguida]

I've attached the actual excel file I'm trying to complete... I'm trying to get the TOP 3 (Player Names and Totals) for Columns C, J & L

[NBVC]

In N2 enter:

=INDEX($A$2:$A$13,SMALL(IF($C$2:$C$13=O2,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$2:O2,O2)))

confirm with CTRL+SHIFT+ENTER, then copy down to N4.

In N6 enter:

=INDEX($A$2:$A$13,SMALL(IF($J$2:$J$13=O6,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$6:O6,O6)))


confirm with CTRL+SHIFT+ENTER, then copy down to N8.

In N10:

=INDEX($A$2:$A$13,SMALL(IF($L$2:$L$13=O10,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$10:O10,O10)))

confirm with CTRL+SHIFT+ENTER, then copy down to N12.

[vguida]

Well that worked like a charm!

Thanks!