Index Match Countif Distinct unique values with two or more match columns with result in two different tables.

Addictions

Board Regular
Joined
May 27, 2018
Messages
58
Office Version
  1. 365
Hello,

I wonder if anybody can help me out.

I am trying to get employees names based on their skills columns and return their names as a result in two different tables as a distinct unique values.
I was able to do this so far with Index, Match and Countif array formula but it only works for one skills column and I cannot figure it out how to do it if there are more skills columns.

Basically if employee has two skills allocated then as a result they would show up on both tables. Please see example of the table below and required results.

ABC
1EmployeeSkill 1Skill 2
2JonDriverMechanic
3MichaelMechanic
4BenDriver
5TonyMechanicDriver

<tbody>
</tbody>

DriverMechanic
JonJon
BenMichael
TonyTony

<tbody>
</tbody>

Please if somebody can advise I would much appreciate your help.

Kind regards,
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Employee</td><td style=";">Skill 1</td><td style=";">Skill 2</td><td style="text-align: right;;"></td><td style=";">Driver</td><td style=";">Mechanic</td><td style=";">Mate</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Jon</td><td style=";">Driver</td><td style=";">Mechanic</td><td style="text-align: right;;"></td><td style=";">Jon</td><td style=";">Jon</td><td style=";">Ben</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Michael</td><td style=";">Mechanic</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Ben</td><td style=";">Michael</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Ben</td><td style=";">Driver</td><td style=";">mate</td><td style="text-align: right;;"></td><td style=";">Tony</td><td style=";">Tony</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Tony</td><td style=";">Mechanic</td><td style=";">Driver</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Lookup</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$5,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$A$2:$A$5</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>)/(<font color="Purple">$B$2:$C$5=E$1</font>),ROWS(<font color="Purple">E$2:E2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Formula copied down & across
 

Addictions

Board Regular
Joined
May 27, 2018
Messages
58
Office Version
  1. 365
Thank you very much. It works like a charm.

I need to really understand how it works as I never used aggregate and Row/Rows before.

Thank you it is much appreciated.
 

Addictions

Board Regular
Joined
May 27, 2018
Messages
58
Office Version
  1. 365
Would anybody possibly know how to get similar results with index, match possibly?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,416
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,146
Messages
5,527,084
Members
409,743
Latest member
adamyang24

This Week's Hot Topics

Top