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.

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

<tbody>
</tbody>

 

[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.