Pull top 3 values and Row Headings

[vguida]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Players
[/TD]
[TD]At Bats
[/TD]
[TD]Batting Avg
[/TD]
[/TR]
[TR]
[TD]Mike
[/TD]
[TD]20
[/TD]
[TD].345
[/TD]
[/TR]
[TR]
[TD]Dave
[/TD]
[TD]18
[/TD]
[TD].266
[/TD]
[/TR]
[TR]
[TD]Nick
[/TD]
[TD]16
[/TD]
[TD].121
[/TD]
[/TR]
[TR]
[TD]Paolo
[/TD]
[TD]12
[/TD]
[TD].215
[/TD]
[/TR]
[TR]
[TD]Vince
[/TD]
[TD]10
[/TD]
[TD].318
[/TD]
[/TR]
</tbody>[/TABLE]

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,

 

[Weazel]

maybe something like....

F2 - =INDEX($A$2:$A$6,SMALL(IF($C$2:$C$6=G2,ROW($A$2:$A$6)-ROW($A$2)+1),COUNTIF($G$2:G2,G2))) Control Shift Enter this would take into consideration the event of players having duplicate averages, though I'm not sure if that's a real concern

G2 - =LARGE($C$2:$C$6,E2)

[TABLE="class: grid, width: 477"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Players</SPAN>
[/TD]
[TD]At Bats</SPAN>
[/TD]
[TD]Batting Avg</SPAN>
[/TD]
[TD][/TD]
[TD]Top 3</SPAN>
[/TD]
[TD]Player</SPAN>
[/TD]
[TD]Avg.</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Mike</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]0.345</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD]Mike</SPAN>
[/TD]
[TD="align: right"]0.345</SPAN>
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Dave</SPAN>
[/TD]
[TD]18</SPAN>
[/TD]
[TD]0.318</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD]Dave</SPAN>
[/TD]
[TD="align: right"]0.318</SPAN>
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Nick</SPAN>
[/TD]
[TD]16</SPAN>
[/TD]
[TD]0.121</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD]Vince</SPAN>
[/TD]
[TD="align: right"]0.318</SPAN>
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Paolo</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]0.215</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Vince</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0.318</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]