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:
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 |
