Compare tables

[sharshra]

I have to compare 2 tables & do some checks. I'm trying to do this using dynamic arrays, but getting errors. Can the experts help to resolve this please?

I have a source table & a check table. I have to verify the roles in the source table against check table. If there is any match found in the check table, add that role in the 3rd column in the source table.

Consider the following example. Source table has name, role columns. Name abc has no matching role in the check table. So, 3rd column is blank. User cds has matching roles 2 & 3 in the check table. So, add 2,3 in the 3rd column in source table. Continue the same for the remaining names & roles. Final output should look like as shown below.

Source table:

excel problems.xlsx
	C	D
11	name	role
12	abc	1,9,23,6
13	cds	2,9,4,8,3
14	omk	7,5
15	jn	8
16	ipugb	11,5
17	hfnl	43,8,9,2
18	trhjvm	12,4
19	kmjh	9,5,7
20	rfvgh	2
21	rd	3,11
Sheet9

Check table:

excel problems.xlsx
	H
11	check role
12	2
13	3
14	43
15	5
Sheet9

Output in source table:

excel problems.xlsx
	C	D	E
11	name	role	user check role
12	abc	1,9,23,6
13	cds	2,9,4,8,3	2,3
14	omk	7,5	5
15	jn	8
16	ipugb	11,5	5
17	hfnl	43,8,9,2	43,2
18	trhjvm	12,4
19	kmjh	9,5,7	5
20	rfvgh	2	2
21	rd	3,11	3
Sheet9

 

[saboh12617]

Hello,
A proposition on XL365 (in E12):

=LET(toCheck;D12:D21;roles;H12:H15;
BYROW(toCheck;LAMBDA(r;TEXTJOIN(",";TRUE;MAP(TEXTSPLIT(r;",");LAMBDA(n;XLOOKUP(VALUE(n);roles;roles;"")))))))

 

[saboh12617]

Edit : sorry formula was with ";" separators, here is the "," one

=LET(toCheck, D12:D21, roles, H12:H15, 
BYROW(toCheck, LAMBDA(r, TEXTJOIN(",", TRUE, MAP(TEXTSPLIT(r, ","), LAMBDA(n, XLOOKUP(VALUE(n), roles, roles, "")))))))

 

[Fluff]

Another option

=MAP(D12:D21,LAMBDA(m,LET(s,--TEXTSPLIT(m,","),TEXTJOIN(",",,FILTER(s,ISNUMBER(XMATCH(s,H12:H20)),"")))))

 

[sharshra]

Thank you very much, @saboh12617 Really appreciate your help

 

[sharshra]

Another option

=MAP(D12:D21,LAMBDA(m,LET(s,--TEXTSPLIT(m,","),TEXTJOIN(",",,FILTER(s,ISNUMBER(XMATCH(s,H12:H20)),"")))))

Thanks, @Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.