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

[Addictions]

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.

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee[/TD]
[TD]Skill 1[/TD]
[TD]Skill 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jon[/TD]
[TD]Driver[/TD]
[TD]Mechanic[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Michael[/TD]
[TD]Mechanic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ben[/TD]
[TD]Driver[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tony[/TD]
[TD]Mechanic[/TD]
[TD]Driver[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 200"]
<tbody>[TR]
[TD]Driver[/TD]
[TD]Mechanic[/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD]Jon[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Michael[/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD]Tony[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Kind regards,

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G
1	Employee	Skill 1	Skill 2		Driver	Mechanic	Mate
2	Jon	Driver	Mechanic		Jon	Jon	Ben
3	Michael	Mechanic			Ben	Michael
4	Ben	Driver	mate		Tony	Tony
5	Tony	Mechanic	Driver
Lookup

Cell Formulas
Range	Formula
E2	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/($B$2:$C$5=E$1),ROWS(E$2:E2))),"")

Formula copied down & across

 

[Addictions]

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]

Would anybody possibly know how to get similar results with index, match possibly?

 

[Fluff]

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

Have a look here, whilst it not quite the same formula, it's very similar https://exceljet.net/formula/extract-all-partial-matches

 

[Addictions]

Thank you, that's very helpful.

 

[Fluff]

You're welcome & thanks for the feedback