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

Dlloyd15

New Member
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.

(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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
=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

New Member
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

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

Dlloyd15

New Member
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

MrExcel MVP, Moderator
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?

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

AA_MrExcel 20 02 20.xlsm
CDE
10Last NameOfficeCo2 Emission
11Name1GS0
12Name2GS100
13Name3GS105
14Name4GS100
15Name5GS110
3 lowest (2)

Dlloyd15

New Member
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

MrExcel MVP, Moderator
And what would you want to show for something like

ZFluff.xlsm
ABCDEFG
1
2
3
4
5
6
7
8GS
91Crawford,Parker,Mason,Hill
10First NameLast NameOfficeCo2 Emission2Crawford,Parker,Mason,Hill
11OliverCaseyIN7573Crawford,Parker,Mason,Hill
12AidaCrawfordGS0
13WilliamEllisGS108
14NatalieArmstrongMD119
15RosieBakerMD89
16StellaBrooksIN105
17OscarParkerGS0
18NatalieMorrisIN115
19AlenGibsonGS105
20BriannaEvansMD111
21AmberJonesNT133
22MirandaArmstrongNT104
23BrookeMasonGS0
24MyraHillGS0
25RyanHillMD117
26RafaelGrantMD117
27LuciaHillIN256
28JuliaMasonGS128
29EdwardBaileyWW169
30AidenMurrayWW119
31LilyReedLE112
32KellanGibsonIN0
33DarylSullivanIN138
34DexterScottGS99
35MartinRobertsMD127
Data
Cell Formulas
RangeFormula
G9:G11G9=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

New Member
Thanks Fluff! So in your example

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

Would be what I'm trying to achieve

Fluff

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

ZFluff.xlsm
ABCDEFGH
1
2
3
4
5
6
7
8GS
91Crawford,Parker,Mason,Hill
10First NameLast NameOfficeCo2 Emission8Scott
11OliverCaseyIN75711Gibson
12AidaCrawfordGS01
13WilliamEllisGS10815
14NatalieArmstrongMD119
15RosieBakerMD89
16StellaBrooksIN105
17OscarParkerGS01
18NatalieMorrisIN115
19AlenGibsonGS10511
20BriannaEvansMD111
21AmberJonesNT133
22MirandaArmstrongNT104
23BrookeMasonGS01
24MyraHillGS01
25RyanHillMD117
26RafaelGrantMD117
27LuciaHillIN256
28JuliaMasonGS12829
29EdwardBaileyWW169
30AidenMurrayWW119
31LilyReedLE112
32KellanGibsonIN0
33DarylSullivanIN138
34DexterScottGS998
35MartinRobertsMD127
36MichelleEllisIN209
37JustinWrightIN91
39RichardEllisIN134
40ConnieHarperIN106
41ValeriaBrownIN106
42BrionyPerryGS12427
43CarlMorrisIN120
45AprilClarkGS11319
46BrookeAlexanderIN129
47BriannaColeIN146
48PenelopeChapmanIN294
49KevinDavisWW103
50PaigeMitchellGS11217
Data
Cell Formulas
RangeFormula
G9G9=SMALL(F\$11:F\$77,1)
H9:H11H9=TEXTJOIN(",",1,IF(F\$11:F\$50=G9,C\$11:C\$50,""))
G10G10=AGGREGATE(15,6,F\$11:F\$50/(F\$11:F\$50<>G9),1)
G11G11=AGGREGATE(15,6,F\$11:F\$50/(F\$11:F\$50<>G10)/(F\$11:F\$50<>G9),1)
F11:F50F11=IF(D11=H\$8,COUNTIF(E\$11:E\$77,"<"&E11)+1,"")

Otherwise I cannot help.

1,089,514
Messages
5,408,722
Members
403,223
Latest member
rholmesa