Index and Match - Can I have 2 or 3 results show in one cell?

[Mel Smith]

Hello folks,

I use this formula: =INDEX(B4:B63,MATCH(MAX(H4:H63),H4:H63,0),1) to show the "top" performer and it works well but my question is, can it be adapted to show, say 2 or even 3 names who are equal "top"? When there are 2 equal, it always shows the first in the list and I would like to be able to show both names.

Thanks for your help, as always.

Mel

 

[Fluff]

How about

=TEXTJOIN(", ",,FILTER(B4:B63,H4:H63=MAX(H4:H63)))

 

[Mel Smith]

=TEXTJOIN(", ",,FILTER(B4:B63,H4:H63=MAX(H4:H63)))

Absolutely perfect. Thank you.

Mel

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mel Smith]

Hi Fluff,

How may I amend your formula to list the lowest 2 or 3 in a list whilst excluding all those with 0?

Mel

 

[Fluff]

Can you post some sample data, including expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Mel Smith]

Hi Fluff,

Does this help?

Mel

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
3		Ovs	Mdns	Runs	Wkts	Ave	Best	Strike rate	Economy	5 wkt innings	10 wkt match	Wide balls bowled	No balls bowled
4	Bethell JG	25.5	2	82	4	20.5	3/10	39.25	3.17	0	0	1(1)	1(2)
5	Bresnan TT					0.0	0	0.00	0.00	0	0
6	Briggs DR					0.0	0	0.00	0.00	0	0
7	Brookes EA	62.0	6	320	3	106.7	1/12	127.33	5.16	0	0		10(28)
8	Brookes HJH	38.0	6	125	4	31.3	2/22	57.50	3.29	0	0		2(4)
9	Burgess MGK					0.0	0	0.00	0.00	0	0
10	Furrer GW					0.0	0	0.00	0.00	0	0
11	Garrett GA	90.0	11	349	3	116.3	2/17	181.67	3.88	0	0		5(14)
12	Hain SR					0.0	0	0.00	0.00	0	0
13	Hannon-Dalby OJ					0.0	0	0.00	0.00	0	0
14	Hose AJ					0.0	0	0.00	0.00	0	0
15	Johal MS	105.0	18	338	9	37.6	2/12	70.56	3.22	0	0		5(10)
16	Lamb MJ	10.0	1	20		0.0	0/20	0.00	2.00	0	0
17	Miles CN					0.0	0	0.00	0.00	0	0
18	Mousley DR	37.0	7	158	5	31.6	4/31	44.40	4.27	0	0
19	Norwell LC	54.5	9	161	9	17.9	4/24	36.56	2.94	0	0
20	Pollock EJ					0.0	0	0.00	0.00	0	0
21	Rhodes WMH					0.0	0	0.00	0.00	0	0
22	Sibley DP					0.0	0	0.00	0.00	0	0
23	Sidebottom RN	81.0	23	235	8	29.4	4/18	61.38	2.90	0	0		5(10)
24	Stone OP	14.0	1	60	5	12.0	5/20	16.80	4.29	1	0
25	Thomson AT					0.0	0	0.00	0.00	0	0
26	McGladdery T R	0.1		4		0.0	0/4	0.00	24.00	0	0
27	Bulpitt J	21.0	5	49	5	9.8	5/49	25.20	2.33	1	0
28	Shaikh H					0.0	0	0.00	0.00	0	0
29	Smith K					0.0	0	0.00	0.00	0	0
30
31	Best average:			Bulpitt J	9.80
32	Best strike rate:			Stone OP	16.80
33	Best economy rate:			Lamb MJ	2.00
Second XI Championship

Cell Formulas
Range	Formula
E31	E31	=MINIFS(F4:F29,F4:F29,">0")
E32	E32	=MINIFS(H5:H29,H5:H29,">0")
E33	E33	=MINIFS(I6:I29,I6:I29,">0")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E29	Cell Value	top 1 values	text	NO
F4:F29	Expression	=F4=MINIFS($F$4:$F$29,$F$4:$F$29,">"&0)	text	NO
H4:H29	Expression	=H4=MINIFS($H$4:$H$29,$H$4:$H$29,">"&0)	text	NO
I4:I29	Expression	=I4=MINIFS($I$4:$I$29,$I$4:$I$29,">"&0)	text	NO

 

[Fluff]

And what result are you looking for?

 

[Mel Smith]

Sorry. Best Average - Bulpitt J. Best Strike rate - Stone OP. Best economy - Lamb MJ

Mel

 

[Fluff]

Ok, how about

=TEXTJOIN(", ",,FILTER(A4:A29,F4:F29=E31))