Max value in Different Columns

[grkchakri]

Hi All

i have a table with sales persons and there sales across years from 2001-2030

i have a scroll bar which will give years in 5 columns (This step is a larger part of file i am preparing)

Ask : When i click the scroll bar the year in the columns(G19:L19) change subsequently based on i want to have highest number of sale in that year
in the next row to each column

I tried using max, maxifs, index, match, but i am unable to connect and get the correct result. file attached

Your advise is much appreciated.

[JohnTopley]

This ??

in G20

Please Login or Register  to view this content.

Copy across

[alansidman]

An alternative is with Power query

Excel 2016 (Windows) 64 bit

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
22	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016
23	H	B	E	C	H	L	K	I	C	M	H	B	L	B	B	M
24	921	903	995	927	866	995	956	845	979	858	887	967	916	987	945	840

Sheet: Sheet1

See attached

[alansidman]

Thanks for the Rep.

[CARACALLA]

G19=INDEX($B$2:$B$14,MATCH(G$20,INDEX($C$2:$R$14,,MATCH(G$18,VALUE(Yeardata),0)),0))

Copy across


G20=AGGREGATE(14,6,Table1[[2001]:[2016]]/(VALUE(Yeardata)=G$18),1)

Copy across