Excel formula to find value in multiple dynamic arrays

[radub]

Good evening,
i've been struggling to find a solution to this, but no solution so far :|
Is there a chance I can use Excel 365 formula to find a value in multiple dynamic arrays, and return the array name?
Something like this :

dARR1: one, two , three
dARR2: two, three, five, eight, dARR1
dARR3: two, six, dARR2
dARR4: eight, nine, three, one, two, dARR3
dARR5: two, five, one, six

result:
six: belongs to dARR3, dARR4, dARR5

Thank you,

 

[radub]

Hi Jim & sorry for overcomplicating things.
I've put this another way, maybe I'll manage to explain it better
I have many groups of people and I'm trying to determine the group(s) each person belongs to.
The tricky part is when groups are nested in another groups like bellow:

Book1
	A	B
1	initial data
2	groupName	Members
3	group1	John, Anne, Marie
4	group2	Josh,Alex,group1
5	group3	Adele,Ema
6	group4	Keith,Ed,Scott,group2
7	group5	Valerie,Luci,group4
8
9
10	RESULT
11	Person	Group(s)
12	Ema	group3
13	Alex	group2, group4, group5
14	John	group1, group2, group4, group5
Sheet1

Thank you,

actually, the real data are in table I pasted earlier but it's not very obvious, so I used the person names to make it a little bit more clear. If you can set me on the right track using the person names data, I hope I can take it from there. I paste below the data from above, removing the additional info, so it is in same format as the personal names example
Thank you,

Book1
	A	B
1	groupName	Members
2	HELX_SL_TT	HX-SL-01-TT
3	HELX_SO_TT	HX-SSO-01-TT
4	HELX_WB_TT	HX-WB-01-TT;HX-WB-02-TT
5	HR_SL_TT	THR-SL-01-TT;HELX_WB_TT
6	IM_WB_UT	IM-WB-01-UT;IM-WB-02-UT;IM-PP-03-UT;IM-PP-04-UT
7	IN_CCK_PP_DV	IS-PP-01-DV;IS-PP-04-DV;IS-PP-02-DV;IS-PP-03-DV;IS-PP-05-DV;IS-PP-06-DV
8	IMAN_PP_DV	ICE-PP-01-DV;IM_WB_UT
9	IMAN_PP_UT	ICE-PP-01-UT
10	IPDS_PP_TT	SLW-PP-01-TT;HELX_WB_TT
11	JA_PP_TT	JIR-PP-02-TT;JIR-PP-03-TT;IMAN_PP_DV;HELX_WB_TT
12	JP_TS_ENI_DC	CBS-VN-01-PD;TS-VN-01-PD;TS-VN-02-PD;TS-VN-03-PD;TS-VN-04-PD;TS-VN-05-PD;TS-VN-06-PD;TS-VN-07-PD;TS-VN-08-PD;TS-VN-09-PD;TS-VN-10-PD;TS-VN-11-PD;TS-VN-12-PD;TS-VN-14-PD
13	KX_PP_TT	KFX-PP-02-TT;KFX-SCN-02-TT;IPDS_PP_TT
Sheet2

 

[OaklandJim]

I am SLOWLY trying to get you some help. No promises.

Does each member only appears once in the members data (not including group names)?

Will there ever be a group with no members?

Are members always separated by semicolons.

 

[OaklandJim]

Another question...Is it possible thata higher indexed group will be listed as part of a lower indexed group. For example, might group 5 be part of group 1?

 

[radub]

I am SLOWLY trying to get you some help. No promises.

Does each member only appears once in the members data (not including group names)?

Will there ever be a group with no members?

Are members always separated by semicolons.

Thank you for your help, Jim! I really apreciate!

1. Does each member only appears once in the members data (not including group names)? -- yes
2. Will there ever be a group with no members? -- no
3. Are members always separated by semicolons. --yes

 

[radub]

Another question...Is it possible thata higher indexed group will be listed as part of a lower indexed group. For example, might group 5 be part of group 1?

yes, this can happen

 

[AhoyNC]

Based on your example with names does this help??

Book1
	A	B
1	initial data
2	groupName	Members
3	group1	John, Anne, Marie
4	group2	Josh,Alex,John,Anne,Marie
5	group3	Adele,Ema
6	group4	Keith,Ed,Scott,Josh,Alex,John,Anne,Marie
7	group5	Valerie,Luci,Keith,Ed,Scott,Josh,Alex,John,Anne,Marie
8
9
10	RESULT
11	Person	Group(s)
12	Ema	group3
13	Alex	group2:group4:group5
14	John	group1:group2:group4:group5
Sheet3

Cell Formulas
Range	Formula
B12:B14	B12	=TEXTJOIN(":",,FILTER($A$3:$A$7,REGEXTEST($B$3:$B$7,A12,1)))

 

[djclements]

Not sure if this is what you're looking for, but you could try the following:

=LET(
   rng, A3:B7,
   grp, TAKE(rng,,1),
   fnλ, LAMBDA(me,mbr,LET(
      inc, BYROW(ISNUMBER(SEARCH(grp,TOROW(mbr))),OR),
      IF(OR(inc),me(me,REDUCE(mbr,FILTER(grp,inc),LAMBDA(a,v,SUBSTITUTE(a,v,XLOOKUP(v,grp,mbr))))),mbr))),
   txt, fnλ(fnλ,TAKE(rng,,-1)),
   arr, TEXTSPLIT(TEXTAFTER(";"&txt,";",SEQUENCE(,MAX(LEN(txt)-LEN(SUBSTITUTE(txt,";",)))+1)),";"),
   GROUPBY(TOCOL(arr,2),TOCOL(IF(ISERROR(arr),arr,grp),2),ARRAYTOTEXT,0,0)
)

fnλ is a recursive lambda function that replaces any group names found in the members list with their corresponding member names and repeats until all subsequent group names are also replaced.

The final result will be a list of all member names with their corresponding groups. You could also use the optional [filter_array] argument of GROUPBY, if desired, to return specific names only; e.g: ISNUMBER(XMATCH(TOCOL(arr,2),{"Alex";"Ema";"John"}))

 

[djclements]

Revised without recursion, but by using the accumulated results (a) as the return_array instead:

=LET(
   rng, A3:B7,
   grp, TAKE(rng,,1),
   mbr, TAKE(rng,,-1),
   txt, REDUCE(mbr,FILTER(grp,BYROW(ISNUMBER(SEARCH(grp,TOROW(mbr))),OR)),LAMBDA(a,v,SUBSTITUTE(a,v,XLOOKUP(v,grp,a)))),
   arr, TEXTSPLIT(TEXTAFTER(";"&txt,";",SEQUENCE(,MAX(LEN(txt)-LEN(SUBSTITUTE(txt,";",)))+1)),";"),
   GROUPBY(TOCOL(arr,2),TOCOL(IF(ISERROR(arr),arr,grp),2),ARRAYTOTEXT,0,0)
)

Also, as an alternative to return the results for the list of names in range A12:A14 only:

=LET(
   rng, A3:B7,
   grp, TAKE(rng,,1),
   mbr, TAKE(rng,,-1),
   txt, REDUCE(mbr,FILTER(grp,BYROW(ISNUMBER(SEARCH(grp,TOROW(mbr))),OR)),LAMBDA(a,v,SUBSTITUTE(a,v,XLOOKUP(v,grp,a)))),
   MAP(A12:A14,LAMBDA(v,ARRAYTOTEXT(FILTER(grp,ISNUMBER(SEARCH(v,txt))))))
)

Hopefully one of these can be adapted to meet your needs. Cheers!

 

[djclements]

One more variation, just for good measure...

=LET(
   rng, A3:B7,
   grp, TAKE(rng,,1),
   mbr, TAKE(rng,,-1),
   txt, REDUCE(mbr,SEQUENCE(ROWS(rng)),LAMBDA(a,v,SUBSTITUTE(a,INDEX(grp,v),INDEX(a,v)))),
   arr, TEXTSPLIT(TEXTAFTER(";"&txt,";",SEQUENCE(,MAX(LEN(txt)-LEN(SUBSTITUTE(txt,";",)))+1)),";"),
   GROUPBY(TOCOL(arr,2),TOCOL(IF(ISERROR(arr),arr,grp),2),ARRAYTOTEXT,0,0)
)

-OR-

=LET(
   rng, A3:B7,
   grp, TAKE(rng,,1),
   mbr, TAKE(rng,,-1),
   txt, REDUCE(mbr,SEQUENCE(ROWS(rng)),LAMBDA(a,v,SUBSTITUTE(a,INDEX(grp,v),INDEX(a,v)))),
   MAP(A12:A14,LAMBDA(v,ARRAYTOTEXT(FILTER(grp,ISNUMBER(SEARCH(v,txt))))))
)