INDEX/LARGE Formula Error

[rck]

I am having trouble with the INDEX/LARGE function in this sheet:

Excel 2000
	A	B	C	D	E	F	G	H	I	J	K
2		District 0	District 1	District 2	District 3	District 4	District 5	District 6	District 7	District 8	Total
3	AM2	93	634	1942	301	33	102	412	69	80	3666
4	AM3	44	918	259	1843	8	61	73	36	15	3257
5	AM5	110	295	83	61	328	1425	171	809	59	3341
6	AM6	141	436	417	107	83	298	1635	284	352	3753
7	Total	388	2283	2701	2312	452	1886	2291	1198	506	14017
8
9	Percentage
10	AM2	23.97%	27.77%	71.90%	13.02%	7.30%	5.41%	17.98%	5.76%	15.81%	26.15%
11	AM3	11.34%	40.21%	9.59%	79.71%	1.77%	3.23%	3.19%	3.01%	2.96%	23.24%
12	AM5	28.35%	12.92%	3.07%	2.64%	72.57%	75.56%	7.46%	67.53%	11.66%	23.84%
13	AM6	36.34%	19.10%	15.44%	4.63%	18.36%	15.80%	71.37%	23.71%	69.57%	26.77%
14
15	Max:	141	918	1942	1843	328	1425	1635	809	352	3753
16	Primary Unit:	AM6	AM3	AM2	AM3	AM5	AM5	AM6	AM5	AM6
17	Secondary Unit:	#REF!	AM2	AM6
2012Calls

I would like tex ($A$3:$A$6) associated with the second highest value in range B3:B6 to be displayed in B17. B17 should contain "AM5", but I would like the formula to populate that, not manual entry.

Thanks in advance!

 

[AhoyNC]

Try:
Just enter formula in B17 and copy across.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		District 0	District 1	District 2	District 3	District 4	District 5	District 6	District 7	District 8	Total
3	AM2	93	634	1942	301	33	102	412	69	80	3666
4	AM3	44	918	259	1843	8	61	73	36	15	3257
5	AM5	110	295	83	61	328	1425	171	809	59	3341
6	AM6	141	436	417	107	83	298	1635	284	352	3753
7	Total	388	2283	2701	2312	452	1886	2291	1198	506	14017
8
9	Percentage
10	AM2	23.97%	27.77%	71.90%	13.02%	7.30%	5.41%	17.98%	5.76%	15.81%	26.15%
11	AM3	11.34%	40.21%	9.59%	79.71%	1.77%	3.23%	3.19%	3.01%	2.96%	23.24%
12	AM5	28.35%	12.92%	3.07%	2.64%	72.57%	75.56%	7.46%	67.53%	11.66%	23.84%
13	AM6	36.34%	19.10%	15.44%	4.63%	18.36%	15.80%	71.37%	23.71%	69.57%	26.77%
14
15	Max:	141	918	1942	1843	328	1425	1635	809	352	3753
16	Primary Unit:	AM6	AM3	AM2	AM3	AM5	AM5	AM6	AM5	AM6
17	Secondary Unit:	AM5	AM2	AM6	AM2	AM6	AM6	AM2	AM6	AM2
Sheet

 

[vogel997]

Try this:
[TABLE="width: 396"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Primary Unit:[/TD]
[TD]=INDEX($A$2:$A$5,MATCH(LARGE(B2:B5,1),B2:B5,0))[/TD]
[/TR]
[TR]
[TD]Secondary Unit:[/TD]
[TD]=INDEX($A$2:$A$5,MATCH(LARGE(B2:B5,2),B2:B5,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[rck]

Thank you, AhoyNC! That worked!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.