Formula to return employee's name in cells adjacent to his/her family members

[realtoast]

Experts, I have a table that has a single column containing the full names of all employees and their respective dependents. There's also a unique identifier (Employee Code) that associates all family members. I need a formula in a column to the left of the name column that places the employee's name adjacent to themselves and also adjacent to their family members.

Here's the current table, with no formulas, followed by the desired result.

Employee Code	EE Name	Name	Relationship
A5A0	[formula here]	SMITH, JOHN JACOB	Employee
A5A0		SMITH, JENNY	Spouse
A5A0		SMITH, TIMOTHY	Child
A5A0		SMITH, AMY	Child
A5A0		SMITH, GARY M	Child
A5A0		SMITH, LILLYE	Child
A1HD		MILLER, SCOTT TIMOTHY	Employee
A58K		MARTINEZ, SHELLY	Employee
A55X		KELLER, MAXWELL	Employee
A59X		JAMES, MARTIN SHORT	Employee
A3S4		CANTELME, JAMES S	Employee
A4PP		VIDOR, DARTH	Employee
A3AO		MCCMILLAN, SCARLET	Employee
A59S		POLLY, TERRENCE	Employee
A59S		POLLY, MARY R	Spouse
A1M3		KIMBAL, MICHELLE A	Employee
A04D		COLLINGSWORTH, ANDREW T	Employee
A04D		COLLINGSWORTH, ELIZABETH	Spouse
A04D		COLLINGSWORTH, SAMUEL K	Child
A04D		COLLINGSWORTH, EMILY	Child
A04D		COLLINGSWORTH, SCOTTY	Child

Here's what it should look like when done:

Employee Code	EE Name	Name	Relationship
A5A0	SMITH, JOHN JACOB	SMITH, JOHN JACOB	Employee
A5A0	SMITH, JOHN JACOB	SMITH, JENNY	Spouse
A5A0	SMITH, JOHN JACOB	SMITH, TIMOTHY	Child
A5A0	SMITH, JOHN JACOB	SMITH, AMY	Child
A5A0	SMITH, JOHN JACOB	SMITH, GARY M	Child
A5A0	SMITH, JOHN JACOB	SMITH, LILLYE	Child
A1HD	MILLER, SCOTT TIMOTHY	MILLER, SCOTT TIMOTHY	Employee
A58K	MARTINEZ, SHELLY	MARTINEZ, SHELLY	Employee
A55X	KELLER, MAXWELL	KELLER, MAXWELL	Employee
A59X	JAMES, MARTIN SHORT	JAMES, MARTIN SHORT	Employee
A3S4	CANTELME, JAMES S	CANTELME, JAMES S	Employee
A4PP	VIDOR, DARTH	VIDOR, DARTH	Employee
A3AO	MCCMILLAN, SCARLET	MCCMILLAN, SCARLET	Employee
A59S	POLLY, TERRENCE	POLLY, TERRENCE	Employee
A59S	POLLY, TERRENCE	POLLY, MARY R	Spouse
A1M3	KIMBAL, MICHELLE A	KIMBAL, MICHELLE A	Employee
A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, ANDREW T	Employee
A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, ELIZABETH	Spouse
A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, SAMUEL K	Child
A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, EMILY	Child
A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, SCOTTY	Child

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Employee Code	EE Name	Name	Relationship
2	A5A0	SMITH, JOHN JACOB	SMITH, JOHN JACOB	Employee
3	A5A0	SMITH, JOHN JACOB	SMITH, JENNY	Spouse
4	A5A0	SMITH, JOHN JACOB	SMITH, TIMOTHY	Child
5	A5A0	SMITH, JOHN JACOB	SMITH, AMY	Child
6	A5A0	SMITH, JOHN JACOB	SMITH, GARY M	Child
7	A5A0	SMITH, JOHN JACOB	SMITH, LILLYE	Child
8	A1HD	MILLER, SCOTT TIMOTHY	MILLER, SCOTT TIMOTHY	Employee
9	A58K	MARTINEZ, SHELLY	MARTINEZ, SHELLY	Employee
10	A55X	KELLER, MAXWELL	KELLER, MAXWELL	Employee
11	A59X	JAMES, MARTIN SHORT	JAMES, MARTIN SHORT	Employee
12	A3S4	CANTELME, JAMES S	CANTELME, JAMES S	Employee
13	A4PP	VIDOR, DARTH	VIDOR, DARTH	Employee
14	A3AO	MCCMILLAN, SCARLET	MCCMILLAN, SCARLET	Employee
15	A59S	POLLY, TERRENCE	POLLY, TERRENCE	Employee
16	A59S	POLLY, TERRENCE	POLLY, MARY R	Spouse
17	A1M3	KIMBAL, MICHELLE A	KIMBAL, MICHELLE A	Employee
18	A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, ANDREW T	Employee
19	A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, ELIZABETH	Spouse
20	A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, SAMUEL K	Child
21	A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, EMILY	Child
22	A04D	COLLINGSWORTH, ANDREW T	COLLINGSWORTH, SCOTTY	Child
Sheet6

Cell Formulas
Range	Formula
B2:B22	B2	=XLOOKUP(A2,$A$2:$A$100,$C$2:$C$100)

 

[realtoast]

Awesome! I suspected XLOOKUP() would be in the solution, but I just can't get my head to understand it.

 

[Fluff]

You're welcome & thanks for the feedback.