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

 

[pto160]

The expected results are Oscar Parker, Myra Hill, Kellan Gibson, Brooke Mason, Aida Crawford

First Name	Last Name	Office	Co2 Emission
Oscar	Parker	GS	0​
Myra	Hill	GS	0​
Kellan	Gibson	IN	0​
Brooke	Mason	GS	0​
Aida	Crawford	GS	0​

 

[pto160]

I'll do the formula without helpers if it's not too long.

 

[Peter_SSs]

The expected results are Oscar Parker, Myra Hill, Kellan Gibson, Brooke Mason, Aida Crawford

So, this is also different in that you are looking for First Name and Last Name, not just Last Name?

Are all the results to be listed in a single cell as before, or one name per cell?

What about ties in this scenario? What is the expected result for this data?

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

 

[pto160]

I would be looking for first and last name. The first name in one cell and the last name in the column beside it.
Thanks for clarifying about ties. In this case 0 is ranked 1 for the 5 people. Here is the screenshot for all of them. My data would be unsorted like the example above and there is no rank column.

Book1
	K
15
Sheet2 (2)

 

[pto160]

See below for XL2BB spreadsheet.

 

[pto160]

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

 

[Peter_SSs]

Since you said your data was unsorted, I have mixed them up.

G8 formula is copied across and down.

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

Cell Formulas
Range	Formula
G8:H20	G8	=IF(ROWS(G$8:G8)>COUNTIF($E$11:$E$35,"<="&INDEX(SORT(UNIQUE($E$11:$E$35),1),5)),"",INDEX(SORTBY(B$11:B$35,$E$11:$E$35,1),ROWS(G$8:G8)))

 

[pto160]

Absolutely brilliant. That's exactly what I'm looking for.

 

[Peter_SSs]

Actually, here is a better solution. Try this formula in cell G8 only. All the results should automatically "spill" into the required cells.

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

Cell Formulas
Range	Formula
G8:H18	G8	=FILTER(SORTBY($B$11:$C$35,$E$11:$E$35,1),SORTBY($E$11:$E$35,$E$11:$E$35,1)<=INDEX(SORT(UNIQUE($E$11:$E$35),1),5))
Dynamic array formulas.

 

[pto160]

That formula works great. I love how the formula spills over. I used the formula
=SUMPRODUCT((J8>=$J$8:$J$18)/COUNTIF($J$8:$J$18,$J$8:$J$18)) to rank the data.