Index/Match Formula to Find Top and Bottom

[FMiller]

I am having some issues where I have a pivot table that I want to use the index/match function to pull top and bottom information, by team For the below example, I am trying to figure out how to Rank by team without having to use a filter. I would have a static list on one worksheet with all the teams and want to reference the pivot to find out the top players by team for % Gain from LY. The % Gain from LY is a calculated field outside of the pivot table and cannot be done within the pivot due to data restraints.

[TABLE="width: 339"]
<tbody>[TR]
[TD]PLAYER[/TD]
[TD]TEAM[/TD]
[TD]YDS[/TD]
[TD]% Gain From LY[/TD]
[/TR]
[TR]
[TD]Julio Jones, WR[/TD]
[TD]ATL[/TD]
[TD="align: right"]1,871[/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD]Antonio Brown, WR[/TD]
[TD]PIT[/TD]
[TD="align: right"]1,834[/TD]
[TD="align: right"]57%[/TD]
[/TR]
[TR]
[TD]DeAndre Hopkins, WR[/TD]
[TD]HOU[/TD]
[TD="align: right"]1,521[/TD]
[TD="align: right"]2%[/TD]
[/TR]
[TR]
[TD]Brandon Marshall, WR[/TD]
[TD]NYJ[/TD]
[TD="align: right"]1,502[/TD]
[TD="align: right"]62%[/TD]
[/TR]
[TR]
[TD]Odell Beckham Jr., WR[/TD]
[TD]NYG[/TD]
[TD="align: right"]1,450[/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD]Allen Robinson, WR[/TD]
[TD]JAX[/TD]
[TD="align: right"]1,400[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Demaryius Thomas, WR[/TD]
[TD]DEN[/TD]
[TD="align: right"]1,304[/TD]
[TD="align: right"]36%[/TD]
[/TR]
[TR]
[TD]A.J. Green, WR[/TD]
[TD]CIN[/TD]
[TD="align: right"]1,297[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Larry Fitzgerald, WR[/TD]
[TD]ARI[/TD]
[TD="align: right"]1,215[/TD]
[TD="align: right"]83%[/TD]
[/TR]
[TR]
[TD]Calvin Johnson, WR[/TD]
[TD]DET[/TD]
[TD="align: right"]1,214[/TD]
[TD="align: right"]35%[/TD]
[/TR]
[TR]
[TD]Mike Evans, WR[/TD]
[TD]TB[/TD]
[TD="align: right"]1,206[/TD]
[TD="align: right"]98%[/TD]
[/TR]
[TR]
[TD]Rob Gronkowski, TE[/TD]
[TD]NE[/TD]
[TD="align: right"]1,176[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]Jarvis Landry, WR[/TD]
[TD]MIA[/TD]
[TD="align: right"]1,157[/TD]
[TD="align: right"]7%[/TD]
[/TR]
[TR]
[TD]Brandin Cooks, WR[/TD]
[TD]NO[/TD]
[TD="align: right"]1,138[/TD]
[TD="align: right"]74%[/TD]
[/TR]
[TR]
[TD]Emmanuel Sanders, WR[/TD]
[TD]DEN[/TD]
[TD="align: right"]1,135[/TD]
[TD="align: right"]53%[/TD]
[/TR]
[TR]
[TD]T.Y. Hilton, WR[/TD]
[TD]IND[/TD]
[TD="align: right"]1,124[/TD]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD]Greg Olsen, TE[/TD]
[TD]CAR[/TD]
[TD="align: right"]1,104[/TD]
[TD="align: right"]52%[/TD]
[/TR]
[TR]
[TD]Delanie Walker, TE[/TD]
[TD]TEN[/TD]
[TD="align: right"]1,088[/TD]
[TD="align: right"]81%[/TD]
[/TR]
[TR]
[TD]Jeremy Maclin, WR[/TD]
[TD]KC[/TD]
[TD="align: right"]1,088[/TD]
[TD="align: right"]13%[/TD]
[/TR]
[TR]
[TD]Amari Cooper, WR[/TD]
[TD]OAK[/TD]
[TD="align: right"]1,070[/TD]
[TD="align: right"]18%[/TD]
[/TR]
[TR]
[TD]Doug Baldwin, WR[/TD]
[TD]SEA[/TD]
[TD="align: right"]1,069[/TD]
[TD="align: right"]45%[/TD]
[/TR]
[TR]
[TD]Sammy Watkins, WR[/TD]
[TD]BUF[/TD]
[TD="align: right"]1,047[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD]Gary Barnidge, TE[/TD]
[TD]CLE[/TD]
[TD="align: right"]1,043[/TD]
[TD="align: right"]78%[/TD]
[/TR]
[TR]
[TD]Allen Hurns, WR[/TD]
[TD]JAX[/TD]
[TD="align: right"]1,031[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Eric Decker, WR[/TD]
[TD]NYJ[/TD]
[TD="align: right"]1,027[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD]John Brown, WR[/TD]
[TD]ARI[/TD]
[TD="align: right"]1,003[/TD]
[TD="align: right"]72%[/TD]
[/TR]
[TR]
[TD]Jordan Matthews, WR[/TD]
[TD]PHI[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD]Willie Snead, WR[/TD]
[TD]NO[/TD]
[TD="align: right"]984[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD]Travis Benjamin, WR[/TD]
[TD]CLE[/TD]
[TD="align: right"]966[/TD]
[TD="align: right"]32%[/TD]
[/TR]
[TR]
[TD]Jordan Reed, TE[/TD]
[TD]WSH[/TD]
[TD="align: right"]952[/TD]
[TD="align: right"]93%[/TD]
[/TR]
[TR]
[TD]Kamar Aiken, WR[/TD]
[TD]BAL[/TD]
[TD="align: right"]944[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]Michael Crabtree, WR[/TD]
[TD]OAK[/TD]
[TD="align: right"]922[/TD]
[TD="align: right"]89%[/TD]
[/TR]
[TR]
[TD]James Jones, WR[/TD]
[TD]GB[/TD]
[TD="align: right"]890[/TD]
[TD="align: right"]11%[/TD]
[/TR]
[TR]
[TD]Travis Kelce, TE[/TD]
[TD]KC[/TD]
[TD="align: right"]875[/TD]
[TD="align: right"]6%[/TD]
[/TR]
[TR]
[TD]Zach Ertz, TE[/TD]
[TD]PHI[/TD]
[TD="align: right"]853[/TD]
[TD="align: right"]84%[/TD]
[/TR]
[TR]
[TD]Michael Floyd, WR[/TD]
[TD]ARI[/TD]
[TD="align: right"]849[/TD]
[TD="align: right"]41%[/TD]
[/TR]
[TR]
[TD]Terrance Williams, WR[/TD]
[TD]DAL[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]95%[/TD]
[/TR]
[TR]
[TD]Randall Cobb, WR[/TD]
[TD]GB[/TD]
[TD="align: right"]829[/TD]
[TD="align: right"]78%[/TD]
[/TR]
[TR]
[TD]Benjamin Watson, TE[/TD]
[TD]NO[/TD]
[TD="align: right"]825[/TD]
[TD="align: right"]29%[/TD]
[/TR]
[TR]
[TD]Marvin Jones, WR[/TD]
[TD]CIN[/TD]
[TD="align: right"]816[/TD]
[TD="align: right"]66%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Meesam_ali]

If you want to get the max "% Gain from LY" use the below formula
=INDEX($C$4:$C$43,MATCH(MAX($F$4:$F$43),$F$4:$F$43,0))

And below for minimum value

=INDEX($C$4:$C$43,MATCH(MAX($F$4:$F$43),$F$4:$F$43,0))

Here definitely C4:C43 is the range of names and F4:F43 is the column showing "% gain" values.