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

 

[Peter_SSs]

I used the formula
=SUMPRODUCT((J8>=$J$8:$J$18)/COUNTIF($J$8:$J$18,$J$8:$J$18)) to rank the data.

Could we see what is in J8:J18?

 

[pto160]

Here it is.

Formulas.xlsx
	J	K
7	Co2 Emission	Rank
8	0	1
9	0	1
10	0	1
11	0	1
12	0	1
13	89	2
14	99	3
15	104	4
16	105	5
17	105	5
18	105	5
Sheet3

Cell Formulas
Range	Formula
K8:K18	K8	=SUMPRODUCT((J8>=$J$8:$J$18)/COUNTIF($J$8:$J$18,$J$8:$J$18))

 

[Peter_SSs]

Another option for that..

Dlloyd15 2020-02-22 1.xlsm
	J	K
7	Co2 Emission	Rank
8	0	1
9	0	1
10	0	1
11	0	1
12	0	1
13	89	2
14	99	3
15	104	4
16	105	5
17	105	5
18	105	5
Sheet1

Cell Formulas
Range	Formula
K8:K18	K8	=N(K7)+(J8<>J7)

 

[pto160]

Thanks. That formula is much easier to use.

 

[Peter_SSs]

Thanks. That formula is much easier to use.

Cheers.

Also, since column J is the result of a 'spill' from a dynamic array formula, that list could be longer or shorter, so this, copied down as far as you might ever need might be better.

=IF(J8="","",N(K7)+(J8<>J7))

 

[pto160]

Thanks. That works great.