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,

 

[OaklandJim]

Where in your worksheet does the data and result go?

Maybe provide the data using the excellent Mr. Excel add-in that enables you to post part of a worksheet to the list. See this link for the add in. XL2BB Info.

 

[OaklandJim]

Are numbers 10+ expessed as text (e.g. ten, eleven)?

 

[radub]

Are numbers 10+ expessed as text (e.g. ten, eleven)?

yes. members are text

 

[radub]

Where in your worksheet does the data and result go?

Maybe provide the data using the excellent Mr. Excel add-in that enables you to post part of a worksheet to the list. See this link for the add in. XL2BB Info.

the actual data look like bellow. the dynamic arrays i mentioned, is the textsplit() output for the Members, column.

Book1
	A	B	C	D
1	groupName	count	Members
2	HELX_SL_TT	1	HX-SL-01-TT	HX-SL-01-TT
3	HELX_SO_TT	1	HX-SSO-01-TT	HX-SSO-01-TT
4	HELX_WB_TT	2	HX-WB-01-TT;HX-WB-02-TT	HX-WB-01-TT
5	HR_SL_TT	2	THR-SL-01-TT;HELX_WB_TT	THR-SL-01-TT
6	IM_WB_UT	4	IM-WB-01-UT;IM-WB-02-UT;IM-PP-03-UT;IM-PP-04-UT	IM-WB-01-UT
7	IN_CCK_PP_DV	6	IS-PP-01-DV;IS-PP-04-DV;IS-PP-02-DV;IS-PP-03-DV;IS-PP-05-DV;IS-PP-06-DV	IS-PP-01-DV
8	IMAN_PP_DV	2	ICE-PP-01-DV;IM_WB_UT	ICE-PP-01-DV
9	IMAN_PP_UT	1	ICE-PP-01-UT	ICE-PP-01-UT
10	IPDS_PP_TT;IN_CCK_PP_DV	1	SLW-PP-01-TT	SLW-PP-01-TT
11	JA_PP_TT	2	JIR-PP-02-TT;JIR-PP-03-TT;IMAN_PP_DV;HELX_WB_TT	JIR-PP-02-TT
12	JP_TS_ENI_DC	14	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	CBS-VN-01-PD
13	KX_PP_TT	2	KFX-PP-02-TT;KFX-SCN-02-TT	KFX-PP-02-TT
Sheet1

Cell Formulas
Range	Formula
D2:D3,D9:D10,D13:E13,D12:Q12,D11:G11,D8:E8,D7:I7,D6:G6,D4:E5	D2	=TEXTSPLIT(C2,";")

 

[OaklandJim]

That data in the most recent post is a lot different than the data in the original post. I STARTED to develop some code to address the original data which I thought I understood. What you are looking for is not clear at all, to me at least. Maybe try to be more specific about what the data is and what you need (i.e., how the results are arrived at and what the results should look like.)

 

[radub]

That data in the most recent post is a lot different than the data in the original post. I STARTED to develop some code to address the original data which I thought I understood. What you are looking for is not clear at all, to me at least. Maybe try to be more specific about what the data is and what you need (i.e., how the results are arrived at and what the results should look like.)

Thank you Jim & sorry for the confusion.
I have a text where I need to replace some values with the name of the group the values belong to.
the tricky part is that, some of the groups are members in another groups.

 

[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,

 

[OaklandJim]

Ok. I THINK that I get it now. Might you provide some more data to work with?

 

[OaklandJim]

I bet that names include first and last name? Definitely show realistic data to work with.