Top 5 States With Values

[ZSchoonover]

So I've been researching all day on how to do this, and I'm a bit confused on how to proceed. I need to create multiple top 5 list of States from a data series. I've included a screenshot to help better understand what it I'm trying to achieve. In short, I need excel to auto calculate the top 5 states for students who have applied for spring and fall semesters and return the state name next to the number itself. This info will be populated into 2 separate areas of the sheet C11:D15 and C35:D38. There will be duplicates included with this.

[/URL][/IMG]

The range L2:L51 is labeled "States", M2:M51 is "Spring18", and N2:N51 is "Fall18". This list is dynamic and will be changing quite often so the formulas that calculate the top 5 need to be dynamic as well. I've not had to go this far in depth with excel in years and as such this is the first time I've dealt with arrays and lengthy functions, so I apologize in advance if I'm missing any pertinent information. I'm also using excel 2016/excel 365.

Thanks,
Zach

 

[ZSchoonover]

	201820								State	201820	201840
		AC/AK	7								AK	0	0
		HE	8								AL	0	0
		WD	4								AR	0	0
		RJ	0								AZ	0	0
		Total Apps.	15								CA	0	0
		Av. UG GPA	3.08								CO	0	0
											CT	0	0
	Top 5 States								DE	0	0
		State	Apps								FL	1	2
	1										GA	0	0
	2										HI	0	0
	3										IA	0	0
	4										ID	0	0
	5										IL	0	0
	***Check for repating app								IN	0	1
	numbers for 5th rank***								KS	0	0
											KY	0	0
											LA	0	1
											MA	0	0
											MD	2	1
											ME	0	0
											MI	1	0
	201840								MN	0	0
		AC/AK	5								MO	0	0
		HE	15								MS	0	0
		WD	3								MT	0	0
		RJ	0								NC	0	1
		Total Apps.	20								ND	0	0
		Av. UG GPA	3.08								NE	0	0
											NH	0	0
	Top 5 States								NJ	0	2
		State	Apps								NM	0	0
	1										NV	0	0
	2										NY	1	0
	3										OH	0	0
	4										OK	0	0
	5										OR	0	0
	***Check for repating app								PA	2	3
	numbers for 5th rank***								RI	0	0
											SC	0	1
											SD	0	0
											TN	0	0
											TX	0	1
											UT	0	0
											VA	8	7
											VT	0	0
											WA	0	0
											WI	0	0
											WV	0	0
											WY	0	0

<tbody>
</tbody>

 

[ZSchoonover]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		201820										State	201820	201840
2			AC/AK	7								AK	0	0
3			HE	8								AL	0	0
4			WD	4								AR	0	0
5			RJ	0								AZ	0	0
6			Total Apps.	15								CA	0	0
7			Av. UG GPA	3.08								CO	0	0
8												CT	0	0
9		Top 5 States										DE	0	0
10			State	Apps								FL	1	2
11		1										GA	0	0
12		2										HI	0	0
13		3										IA	0	0
14		4										ID	0	0
15		5										IL	0	0
16		***Check for repating app										IN	0	1
17		numbers for 5th rank***										KS	0	0
18												KY	0	0
19												LA	0	1
20												MA	0	0
21												MD	2	1
22												ME	0	0
23												MI	1	0
24		201840										MN	0	0
25			AC/AK	5								MO	0	0
26			HE	15								MS	0	0
27			WD	3								MT	0	0
28			RJ	0								NC	0	1
29			Total Apps.	20								ND	0	0
30			Av. UG GPA	3.08								NE	0	0
31												NH	0	0
32		Top 5 States										NJ	0	2
33			State	Apps								NM	0	0
34		1										NV	0	0
35		2										NY	1	0
36		3										OH	0	0
37		4										OK	0	0
38		5										OR	0	0
39		***Check for repating app										PA	2	3
40		numbers for 5th rank***										RI	0	0
41												SC	0	1
42												SD	0	0
43												TN	0	0
44												TX	0	1
45												UT	0	0
46												VA	8	7
47												VT	0	0
48												WA	0	0
49												WI	0	0
50												WV	0	0
51												WY	0	0
1820 & 1840 Charts

Posted using the MrExcelHtml Add-In

 

[Chrisdontm]

I see what I did wrong,

Lets try this in cell: C34

=IF($D34=$D33,INDEX(OFFSET($L$1,MATCH($C33,$L$1:$L$50,0),,51-MATCH($C33,$L$1:$L$51,0),1),MATCH($D34,OFFSET($N$1,MATCH($C33,$L$1:$L$50,0),,51-MATCH($C33,$L$1:$L$51,0),1),0)),INDEX($L$2:$L$51,MATCH($D34,$N$2:$N$51,0)))

Thanks...