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

Dlloyd15

New Member
Joined
Feb 20, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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 NameLast NameOfficeCo2 Emission
OliverCaseyIN157
AidaCrawfordGS214
WilliamEllisGS108
NatalieArmstrongMD119
RosieBakerMD89
StellaBrooksIN105
OscarParkerGS108
NatalieMorrisIN115
AlenGibsonGS105
BriannaEvansMD111
AmberJonesNT133
MirandaArmstrongNT104
BrookeMasonGS133
MyraHillGS165
RyanHillMD117
RafaelGrantMD117
LuciaHillIN256
JuliaMasonGS128
EdwardBaileyWW169
AidenMurrayWW119
LilyReedLE112
KellanGibsonIN0
DarylSullivanIN138
DexterScottGS99
MartinRobertsMD127
MichelleEllisIN209
JustinWrightIN91
BradAllenGS120
RichardEllisIN134
ConnieHarperIN106
ValeriaBrownIN106
BrionyPerryGS124
CarlMorrisIN120
CarlosAdamsIN153
AprilClarkGS113
BrookeAlexanderIN129
BriannaColeIN146
PenelopeChapmanIN294
KevinDavisWW103
PaigeMitchellGS112
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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,""))
 
Upvote 0
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!
 
Upvote 0
You're welcome & thanks for the feedback.
The weekend will definitely be interesting. Probably too close to call for any of the games.
 
Upvote 0
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.
 
Upvote 0
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
CDE
10Last NameOfficeCo2 Emission
11Name1GS0
12Name2GS100
13Name3GS105
14Name4GS100
15Name5GS110
3 lowest (2)
 
Upvote 0
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.
 
Upvote 0
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,""))
 
Upvote 0
Thanks Fluff! So in your example

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

Would be what I'm trying to achieve
 
Upvote 0
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
11OliverCaseyIN757 11Gibson
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 
38BradAllenGS12025
39RichardEllisIN134 
40ConnieHarperIN106 
41ValeriaBrownIN106 
42BrionyPerryGS12427
43CarlMorrisIN120 
44CarlosAdamsIN153 
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.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top