Returning top 3 list with duplicates using INDEX, MATCH, IF, SMALL & TEXTJOIN

[Dlloyd15]

Hi all,

Firstly I'd just like to say I'm fairly new at using extensive functions in Excel, so if I've missed anything or am doing this the completely wrong way then apologies, still trying to get my head round everything.

So my problem:
I have a table with first name, last name, office, and (car) CO2 emissions and am trying to get the name of the employees with the 3 best (lowest) CO2 emissions per office, in this case = GS. But am trying to keep everything tidy so have one big formula in a cell doing all the functions simultaneously. So far I have been able to do this but have hit a block because the 3rd best (lowest) employee is joint 3rd with 108 g/km and as a result the cell is only showing one name rather than the two.

Does anyone know what i need to do to fix this, or if it is possible? Formula and tables below. Hopefully this all makes sense but happy to answer any questions if not/it helps.

Thanks in advance!

(P.S. all names have been changed)

(Array) Formula:

{=(TEXTJOIN(",",TRUE,INDEX($B$11:$E$77,MATCH(1,($E$11:$E$77=SMALL(IF($D$11:$D$77="GS",$E$11:$E$77),3))*($D$11:$D$77="GS"),0),2)))}

This returns 'Ellis' but I need it to return 'Ellis,Parker' as both are the 3rd best CO2 emissions in the 'GS' office.

Table:

First Name	Last Name	Office	Co2 Emission
Oliver	Casey	IN	157
Aida	Crawford	GS	214
William	Ellis	GS	108
Natalie	Armstrong	MD	119
Rosie	Baker	MD	89
Stella	Brooks	IN	105
Oscar	Parker	GS	108
Natalie	Morris	IN	115
Alen	Gibson	GS	105
Brianna	Evans	MD	111
Amber	Jones	NT	133
Miranda	Armstrong	NT	104
Brooke	Mason	GS	133
Myra	Hill	GS	165
Ryan	Hill	MD	117
Rafael	Grant	MD	117
Lucia	Hill	IN	256
Julia	Mason	GS	128
Edward	Bailey	WW	169
Aiden	Murray	WW	119
Lily	Reed	LE	112
Kellan	Gibson	IN	0
Daryl	Sullivan	IN	138
Dexter	Scott	GS	99
Martin	Roberts	MD	127
Michelle	Ellis	IN	209
Justin	Wright	IN	91
Brad	Allen	GS	120
Richard	Ellis	IN	134
Connie	Harper	IN	106
Valeria	Brown	IN	106
Briony	Perry	GS	124
Carl	Morris	IN	120
Carlos	Adams	IN	153
April	Clark	GS	113
Brooke	Alexander	IN	129
Brianna	Cole	IN	146
Penelope	Chapman	IN	294
Kevin	Davis	WW	103
Paige	Mitchell	GS	112

 

[Fluff]

Hi & welcome to MrExcel.
How about
=TEXTJOIN(",",TRUE,IF(($E$11:$E$77=SMALL(IF($D$11:$D$77="GS",$E$11:$E$77),3))*($D$11:$D$77="GS"),C11:C77,""))

 

[Dlloyd15]

Thanks Fluff! I've spent some time trying to teach myself this and then spent a lot of time pulling my hair out trying to figure out how to get this to work, and you've just solved it easily! Thank you!

Never been so grateful to an English Rugby supporter Good luck for the weekend, hopefully you beat Ireland and we beat the French to keep it an open contest!

 

[Fluff]

You're welcome & thanks for the feedback.
The weekend will definitely be interesting. Probably too close to call for any of the games.

 

[Dlloyd15]

Just to follow up on this, I now have 5 employees with 0g/km for CO2 emissions and the same names appear in all top 3 lists, i.e its saying they're all 1st, 2nd and 3rd. Any idea on how to solve this?

Will be very interesting. I would normally back England and Wales at home but it will be very close.

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have the FILTER function in your 365 version then another option would be

=TEXTJOIN(",",TRUE,FILTER(C11:C50,(D11:D50="GS")*(E11:E50=AGGREGATE(15,6,E11:E50/(D11:D50="GS"),3))))

Just to follow up on this, I now have 5 employees with 0g/km for CO2 emissions and the same names appear in all top 3 lists, i.e its saying they're all 1st, 2nd and 3rd. Any idea on how to solve this?

I was just about to ask about this sort of thing. How to solve depends on what results you want to show,

What results for 1st, 2nd and 3rd would you want, and where for this set of data

AA_MrExcel 20 02 20.xlsm
	C	D	E
10	Last Name	Office	Co2 Emission
11	Name1	GS	0
12	Name2	GS	100
13	Name3	GS	105
14	Name4	GS	100
15	Name5	GS	110
3 lowest (2)

 

[Dlloyd15]

Thanks Peter! Unfortunately I'm not able to use the FILTER function.

I assume the function I'm using (thanks to Fluff) is counting the joint numbers then skipping to the next non-joint number, i.e. if the top 5 are on 0g/km then the next rank would be 6 instead of 2, 3, 4, 5 and 6.
Would I be able to use COUNTIF here?

In your example I would like to show that Name1 is 1st rank, Name2 and Name4 are 2nd and then Name 3 is 3rd. But what I'm getting is Name1 is 1st, Name2 and Name4 are 2nd and 3rd.

 

[Fluff]

And what would you want to show for something like

ZFluff.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8							GS
9						1	Crawford,Parker,Mason,Hill
10		First Name	Last Name	Office	Co2 Emission	2	Crawford,Parker,Mason,Hill
11		Oliver	Casey	IN	757	3	Crawford,Parker,Mason,Hill
12		Aida	Crawford	GS	0
13		William	Ellis	GS	108
14		Natalie	Armstrong	MD	119
15		Rosie	Baker	MD	89
16		Stella	Brooks	IN	105
17		Oscar	Parker	GS	0
18		Natalie	Morris	IN	115
19		Alen	Gibson	GS	105
20		Brianna	Evans	MD	111
21		Amber	Jones	NT	133
22		Miranda	Armstrong	NT	104
23		Brooke	Mason	GS	0
24		Myra	Hill	GS	0
25		Ryan	Hill	MD	117
26		Rafael	Grant	MD	117
27		Lucia	Hill	IN	256
28		Julia	Mason	GS	128
29		Edward	Bailey	WW	169
30		Aiden	Murray	WW	119
31		Lily	Reed	LE	112
32		Kellan	Gibson	IN	0
33		Daryl	Sullivan	IN	138
34		Dexter	Scott	GS	99
35		Martin	Roberts	MD	127
Data

Cell Formulas
Range	Formula
G9:G11	G9	=TEXTJOIN(",",TRUE,IF(($E$11:$E$77=SMALL(IF($D$11:$D$77=$G$8,$E$11:$E$77),$F9))*($D$11:$D$77="GS"),$C$11:$C$77,""))

 

[Dlloyd15]

Thanks Fluff! So in your example

1 - Crawford, Parker, Mason, Hill
2 - Scott
3 - Gibson

Would be what I'm trying to achieve

 

[Fluff]

I had a horrible suspicion you would say that, if you are happy with a couple of helpers.

ZFluff.xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8								GS
9							1	Crawford,Parker,Mason,Hill
10		First Name	Last Name	Office	Co2 Emission		8	Scott
11		Oliver	Casey	IN	757		11	Gibson
12		Aida	Crawford	GS	0	1
13		William	Ellis	GS	108	15
14		Natalie	Armstrong	MD	119
15		Rosie	Baker	MD	89
16		Stella	Brooks	IN	105
17		Oscar	Parker	GS	0	1
18		Natalie	Morris	IN	115
19		Alen	Gibson	GS	105	11
20		Brianna	Evans	MD	111
21		Amber	Jones	NT	133
22		Miranda	Armstrong	NT	104
23		Brooke	Mason	GS	0	1
24		Myra	Hill	GS	0	1
25		Ryan	Hill	MD	117
26		Rafael	Grant	MD	117
27		Lucia	Hill	IN	256
28		Julia	Mason	GS	128	29
29		Edward	Bailey	WW	169
30		Aiden	Murray	WW	119
31		Lily	Reed	LE	112
32		Kellan	Gibson	IN	0
33		Daryl	Sullivan	IN	138
34		Dexter	Scott	GS	99	8
35		Martin	Roberts	MD	127
36		Michelle	Ellis	IN	209
37		Justin	Wright	IN	91
38		Brad	Allen	GS	120	25
39		Richard	Ellis	IN	134
40		Connie	Harper	IN	106
41		Valeria	Brown	IN	106
42		Briony	Perry	GS	124	27
43		Carl	Morris	IN	120
44		Carlos	Adams	IN	153
45		April	Clark	GS	113	19
46		Brooke	Alexander	IN	129
47		Brianna	Cole	IN	146
48		Penelope	Chapman	IN	294
49		Kevin	Davis	WW	103
50		Paige	Mitchell	GS	112	17
Data

Cell Formulas
Range	Formula
G9	G9	=SMALL(F$11:F$77,1)
H9:H11	H9	=TEXTJOIN(",",1,IF(F$11:F$50=G9,C$11:C$50,""))
G10	G10	=AGGREGATE(15,6,F$11:F$50/(F$11:F$50<>G9),1)
G11	G11	=AGGREGATE(15,6,F$11:F$50/(F$11:F$50<>G10)/(F$11:F$50<>G9),1)
F11:F50	F11	=IF(D11=H$8,COUNTIF(E$11:E$77,"<"&E11)+1,"")

Otherwise I cannot help.