Show top cells data using Index and Match

[deepditch]

I have a table with some data.
I want to show the top 5 names and then add to some text.
Currently i am using:

=INDEX(C6:C21,MATCH(LARGE(F6:F21,{1,2}),F6:F21,0)) &" you are doing a great job in Service Coverage. You are a role mode!"

[TABLE="width: 1264"]
<colgroup><col><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Carol you are doing a great job in Service Coverage. You are a role mode![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B4[/TD]
[TD]B5[/TD]
[TD]B6[/TD]
[TD]B7[/TD]
[TD]B8[/TD]
[TD]B9[/TD]
[TD]B10[/TD]
[TD]B11[/TD]
[TD]B12[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Students[/TD]
[TD]Covered Student No.[/TD]
[TD]Service Coverage[/TD]
[TD]Monthly Target[/TD]
[TD]SeatsTaken UTD[/TD]
[TD]Seat Taken per active Ss[/TD]
[TD]Completion rate[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]12[/TD]
[TD]Jim[/TD]
[TD]112[/TD]
[TD]36[/TD]
[TD]32%[/TD]
[TD]560[/TD]
[TD]206[/TD]
[TD]1.84[/TD]
[TD]37%[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]7[/TD]
[TD]Bob[/TD]
[TD]111[/TD]
[TD]74[/TD]
[TD]67%[/TD]
[TD]470[/TD]
[TD]273[/TD]
[TD]2.46[/TD]
[TD]58%[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]3[/TD]
[TD]Carol[/TD]
[TD]103[/TD]
[TD]98[/TD]
[TD]95%[/TD]
[TD]450[/TD]
[TD]291[/TD]
[TD]2.83[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]A9[/TD]
[TD]6[/TD]
[TD]Tony[/TD]
[TD]99[/TD]
[TD]57[/TD]
[TD]58%[/TD]
[TD]500[/TD]
[TD]303[/TD]
[TD]3.06[/TD]
[TD]61%[/TD]
[/TR]
[TR]
[TD]A10[/TD]
[TD]1[/TD]
[TD]Sue[/TD]
[TD]121[/TD]
[TD]78[/TD]
[TD]64%[/TD]
[TD]510[/TD]
[TD]333[/TD]
[TD]2.75[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]A11[/TD]
[TD]5[/TD]
[TD]Rita[/TD]
[TD]115[/TD]
[TD]83[/TD]
[TD]72%[/TD]
[TD]490[/TD]
[TD]299[/TD]
[TD]2.60[/TD]
[TD]61%[/TD]
[/TR]
[TR]
[TD]A12[/TD]
[TD]2[/TD]
[TD]Mary[/TD]
[TD]104[/TD]
[TD]69[/TD]
[TD]66%[/TD]
[TD]440[/TD]
[TD]285[/TD]
[TD]2.74[/TD]
[TD]65%[/TD]
[/TR]
[TR]
[TD]A13[/TD]
[TD]4[/TD]
[TD]John[/TD]
[TD]122[/TD]
[TD]99[/TD]
[TD]81%[/TD]
[TD]500[/TD]
[TD]318[/TD]
[TD]2.61[/TD]
[TD]64%[/TD]
[/TR]
[TR]
[TD]A14[/TD]
[TD]8[/TD]
[TD]Flora[/TD]
[TD]152[/TD]
[TD]101[/TD]
[TD]66%[/TD]
[TD]600[/TD]
[TD]345[/TD]
[TD]2.27[/TD]
[TD]58%[/TD]
[/TR]
[TR]
[TD]A15[/TD]
[TD]10[/TD]
[TD]Mike[/TD]
[TD]134[/TD]
[TD]91[/TD]
[TD]68%[/TD]
[TD]580[/TD]
[TD]321[/TD]
[TD]2.40[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]A16[/TD]
[TD]9[/TD]
[TD]Tim[/TD]
[TD]129[/TD]
[TD]87[/TD]
[TD]67%[/TD]
[TD]610[/TD]
[TD]341[/TD]
[TD]2.64[/TD]
[TD]56%[/TD]
[/TR]
[TR]
[TD]A17[/TD]
[TD]11[/TD]
[TD]Kirsty[/TD]
[TD]168[/TD]
[TD]114[/TD]
[TD]68%[/TD]
[TD]790[/TD]
[TD]294[/TD]
[TD]1.75[/TD]
[TD]37%[/TD]
[/TR]
[TR]
[TD]A18[/TD]
[TD]14[/TD]
[TD]Beth[/TD]
[TD]148[/TD]
[TD]51[/TD]
[TD]34%[/TD]
[TD]735[/TD]
[TD]240[/TD]
[TD]1.62[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD]A19[/TD]
[TD]13[/TD]
[TD]Eva[/TD]
[TD]96[/TD]
[TD]68[/TD]
[TD]71%[/TD]
[TD]410[/TD]
[TD]134[/TD]
[TD]1.40[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD]A20[/TD]
[TD]16[/TD]
[TD]Alan[/TD]
[TD]103[/TD]
[TD]72[/TD]
[TD]70%[/TD]
[TD]385[/TD]
[TD]118[/TD]
[TD]1.15[/TD]
[TD]31%[/TD]
[/TR]
[TR]
[TD]A21[/TD]
[TD]15[/TD]
[TD]Brian[/TD]
[TD]106[/TD]
[TD]68[/TD]
[TD]64%[/TD]
[TD]360[/TD]
[TD]114[/TD]
[TD]1.08[/TD]
[TD]32%[/TD]
[/TR]
[TR]
[TD]A22[/TD]
[TD]Centre Overall[/TD]
[TD] [/TD]
[TD]737[/TD]
[TD]409[/TD]
[TD]55%[/TD]
[TD]3419[/TD]
[TD]1163[/TD]
[TD] 1.58[/TD]
[TD]34.02%[/TD]
[/TR]
</tbody>[/TABLE]

I now wish to show the top 5 names. what would be best to use instead of Max?

 

[Jon von der Heyden]

My personal preference would be to use a stepped approach, e.g:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
5	Rank	Name	Students	Covered Student No.	Service Coverage	Monthly Target	SeatsTaken UTD	Seat Taken per active Ss	Completion rate			Service Coverage	Name
6	12	Jim	112	36	32%	560	206	1.84	37%		1	95%	Carol
7	7	Bob	111	74	67%	470	273	2.46	58%		2	81%	John
8	3	Carol	103	98	95%	450	291	2.83	65%		3	72%	Rita
9	6	Tony	99	57	58%	500	303	3.06	61%		4	71%	Eva
10	1	Sue	121	78	64%	510	333	2.75	65%		5	71%	Alan
11	5	Rita	115	83	72%	490	299	2.6	61%
12	2	Mary	104	69	66%	440	285	2.74	65%
13	4	John	122	99	81%	500	318	2.61	64%
14	8	Flora	152	101	66%	600	345	2.27	58%
15	10	Mike	134	91	68%	580	321	2.4	55%
16	9	Tim	129	87	67%	610	341	2.64	56%
17	11	Kirsty	168	114	68%	790	294	1.75	37%
18	14	Beth	148	51	34%	735	240	1.62	33%
19	13	Eva	96	68	71%	410	134	1.4	33%
20	16	Alan	103	72	71%	385	118	1.15	31%
21	15	Brian	106	68	64%	360	114	1.08	32%
Sheet1

Cell Formulas
Range	Formula
L6	=LARGE($E$6:$E$21,K6)
M6	{=INDEX($B:$B,SMALL(IF($E$6:$E$21=L6,ROW($E$6:$E$21)),COUNTIF($L$6:L6,L6)),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[deepditch]

Thanks Jon.
I want to be able to drop all 5 names into the text string.

=INDEX(C6:C21,MATCH(LARGE(F6:F21,{1,2}),F6:F21,0)) &" you are doing a great job in Service Coverage. You are a role mode!"

[TABLE="class: cms_table, width: 1264"]
<tbody>[TR]
[TD]Current
[/TD]
[TD]Carol you are doing a great job in Service Coverage. You are a role mode!


[/TD]
[/TR]
</tbody>[/TABLE]
Needed Carol, John, Rita, Eva and Alan are doing a great ..................