Unique Textjoin with XLookup

[gronfors]

I'm trying to utilize all the 365 functions today it seems!

End goal is to have an employee's supervisor listed based on their team and remove duplicates as some supervisors have multiple teams and some employees are considered on multiple teams.

Right now I have a separate table finding each supervisor an assigning it to a team. Then the primary table checks the employee's team, and lookups up the current supervisor in the table. I've then just messily duplicated that for up to 4 teams but I can't figure out how to work UNIQUE into this or MATCH.

Essentially I'd want Employee 1 to just have Supervisor1 listed instead of duplicated (and employee4 just have Supervisor5 & Supervisor1 listed)

Thanks for the help!

Teams.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Position	Team	Supervisor	Helper	Team1	Team2	Team3	Team4	Leave		Team	Team with Wildcard	Supervisor
2	Employee1	Employee	Unit A & Unit B	Supervisor1 & Supervisor1	No	Unit A	Unit B					Unit A	*Unit A*	Supervisor1
3	Employee2	Employee	Unit A & Unit D	Supervisor1 & Supervisor3	No	Unit A	Unit D					Unit B	*Unit B*	Supervisor1
4	Employee3	Employee	Unit D	Supervisor3	No	Unit D						Unit C	*Unit C*	Supervisor2
5	Employee4	Employee	Unit E & Unit A & Unit B	Supervisor5 & Supervisor1 & Supervisor1	No	Unit E	Unit A	Unit B				Unit D	*Unit D*	Supervisor3
6	Employee5	Employee	Unit F	N/A	No	Unit F						Unit E	*Unit E*	Supervisor5
7	Supervisor1	Supervisor	Unit A & Unit B	N/A	No	Unit A	Unit B
8	Supervisor2	Supervisor	Unit C	N/A	No	Unit C
9	Supervisor3	Supervisor	Unit D	N/A	No	Unit D
10	Supervisor4	Supervisor	Unit E	N/A	On Leave	Unit E				On leave
11	Supervisor5	Supervisor	Unit E	N/A	No	Unit E
Users

Cell Formulas
Range	Formula
D2:D11	D2	=IFERROR(IF(OR([@Position]="Manager",[@Position]="Supervisor"),"N/A",TEXTJOIN(" & ",TRUE,XLOOKUP([@Team1],L:L,N:N),IF([@Team2]="","",XLOOKUP([@Team2],L:L,N:N)),IF([@Team3]="","",XLOOKUP([@Team3],L:L,N:N)),IF([@Team4]="","",XLOOKUP([@Team4],L:L,N:N)))),"N/A")
E2:E11	E2	=IF([@Leave]="On Leave", "On Leave", "No")
F2:F11	F2	=TRIM(IFERROR(LEFT([@Team],FIND("&",[@Team])-1),[@Team]))
G2:G11	G2	=TRIM(IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))<1,"",IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))=1,RIGHT([@Team],LEN([@Team]) - FIND("&",[@Team])),MID([@Team],SEARCH("&",[@Team]) + 1, SEARCH("&", [@Team],SEARCH("&",[@Team])+1) - SEARCH("&",[@Team])-1))))
H2:H11	H2	=TRIM(MID(SUBSTITUTE([@Team],"&",REPT(" ",100)),200,100))
I2:I11	I2	=TRIM(MID(SUBSTITUTE([@Team], "&", REPT(" ", 999)), 2999, 999))
M2:M6	M2	="*"&L2&"*"
N2:N6	N2	=IFERROR(XLOOKUP(M2 & "Supervisor" & "No",Users[[#All],[Team]] & Users[[#All],[Position]] & Users[[#All],[Helper]],Users[[#All],[Name]],,2),"N/A")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D	Expression	=OR(D1="No Coaches Listed",D1="N/A")	text	NO
A:J	Expression	=$J1="On Leave"	text	NO

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Position	Team	Supervisor	Helper	Team1	Team2	Team3	Team4	Leave		Team	Team with Wildcard	Supervisor
2	Employee1	Employee	Unit A & Unit B	Supervisor1	No	Unit A	Unit B					Unit A	*Unit A*	Supervisor1
3	Employee2	Employee	Unit A & Unit D	Supervisor1 & Supervisor3	No	Unit A	Unit D					Unit B	*Unit B*	Supervisor1
4	Employee3	Employee	Unit D	Supervisor3	No	Unit D						Unit C	*Unit C*	Supervisor2
5	Employee4	Employee	Unit E & Unit A & Unit B	Supervisor5 & Supervisor1	No	Unit E	Unit A	Unit B				Unit D	*Unit D*	Supervisor3
6	Employee5	Employee	Unit F	N/A	No	Unit F						Unit E	*Unit E*	Supervisor5
7	Supervisor1	Supervisor	Unit A & Unit B	N/A	No	Unit A	Unit B
8	Supervisor2	Supervisor	Unit C	N/A	No	Unit C
9	Supervisor3	Supervisor	Unit D	N/A	No	Unit D
10	Supervisor4	Supervisor	Unit E	N/A	On Leave	Unit E				On leave
11	Supervisor5	Supervisor	Unit E	N/A	No	Unit E
Data

Cell Formulas
Range	Formula
D2:D11	D2	=IF(OR([@Position]="Manager",[@Position]="Supervisor"),"N/A",TEXTJOIN(" & ",,UNIQUE(XLOOKUP(TRIM(FILTERXML("<k><m>"&SUBSTITUTE([@Team]," & ","</m><m>")&"</m></k>","//m")),Teams[Team],Teams[Supervisor],"N/A",0))))
E2:E11	E2	=IF([@Leave]="On Leave", "On Leave", "No")
F2:F11	F2	=TRIM(IFERROR(LEFT([@Team],FIND("&",[@Team])-1),[@Team]))
G2:G11	G2	=TRIM(IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))<1,"",IF(LEN([@Team]) - LEN(SUBSTITUTE([@Team],"&",""))=1,RIGHT([@Team],LEN([@Team]) - FIND("&",[@Team])),MID([@Team],SEARCH("&",[@Team]) + 1, SEARCH("&", [@Team],SEARCH("&",[@Team])+1) - SEARCH("&",[@Team])-1))))
H2:H11	H2	=TRIM(MID(SUBSTITUTE([@Team],"&",REPT(" ",100)),200,100))
I2:I11	I2	=TRIM(MID(SUBSTITUTE([@Team], "&", REPT(" ", 999)), 2999, 999))
M2:M6	M2	="*"&L2&"*"
N2:N6	N2	=IFERROR(XLOOKUP(M2 & "Supervisor" & "No",Users[[#All],[Team]] & Users[[#All],[Position]] & Users[[#All],[Helper]],Users[[#All],[Name]],,2),"N/A")

 

[gronfors]

That works great, thanks fluff!

 

[Fluff]

You're welcome & thanks for the feedback.