Index match with multiple result

[JAVEDR]

Respected Forum,

I need help for below sample sheet details mention on same.

Thank you for your time and efforts

sample sheet

 

[jasonb75]

Please post your sample to the forum using XL2BB (link in my signature block below) many members of the forum are unable to download attached files for security reasons so your question is likely to go unanswered as it stands.

 

[JAVEDR]

pt.xlsx
	A	B	C
1		123456	YELLOW BOX CONTAINS MORE THAN 3 DIGIT NOT LESS THAN THAT
2	1	245,236
3	2	345,156	ACROSS EACH TEXT I WANT FORMULA THAT MATCH SHEET 2
4	3	346,256	GREEN COLUMN AND RESULT ARE DISPLAYED IN COMMA SEPRATED
5	4	356,135
6	5	456,
7	6	123,
8	7	124,
9	8	134,125
10	9	234,
11	0	145,235
Sheet1

SHEET2

pt.xlsx
	A	B
1	1	128
2	1	137
3	1	236
4	1	678
5	1	245
6	1	290
7	1	470
8	1	579
9	1	380
10	1	335
11	1	588
12	1	489
13	1	344
14	1	399
15	1	100
16	1	155
17	1	560
18	1	146
19	1	669
20	1	119
21	1	227
22	1	777
23	6	123
24	6	268
25	6	367
26	6	178
27	6	240
28	6	259
29	6	457
30	6	790
31	6	880
32	6	330
33	6	358
34	6	448
35	6	899
36	6	349
37	6	600
38	6	556
39	6	150
40	6	114
41	6	466
42	6	169
43	6	277
44	6	222
45	2	129
46	2	147
47	2	246
48	2	679
49	2	345
50	2	390
51	2	480
52	2	589
53	2	570
54	2	255
55	2	200
56	2	138
57	2	336
58	2	688
59	2	660
60	2	110
61	2	156
62	2	778
63	2	237
64	2	228
65	2	499
66	2	444
67	7	124
68	7	179
69	7	467
70	7	269
71	7	890
72	7	458
73	7	340
74	7	359
75	7	250
76	7	557
77	7	700
78	7	368
79	7	133
80	7	188
81	7	115
82	7	566
83	7	160
84	7	278
85	7	223
86	7	377
87	7	449
88	7	999
89	3	120
90	3	157
91	3	256
92	3	670
93	3	139
94	3	148
95	3	346
96	3	689
97	3	247
98	3	779
99	3	229
100	3	445
101	3	599
102	3	490
103	3	300
104	3	580
105	3	355
106	3	337
107	3	238
108	3	788
109	3	166
110	3	111
111	8	170
112	8	567
113	8	125
114	8	260
115	8	189
116	8	468
117	8	369
118	8	134
119	8	477
120	8	279
121	8	224
122	8	459
123	8	990
124	8	440
125	8	800
126	8	558
127	8	350
128	8	378
129	8	288
130	8	233
131	8	116
132	8	666
133	4	130
134	4	158
135	4	680
136	4	356
137	4	239
138	4	248
139	4	347
140	4	789
141	4	167
142	4	112
143	4	266
144	4	149
145	4	446
146	4	699
147	4	400
148	4	455
149	4	590
150	4	220
151	4	770
152	4	257
153	4	338
154	4	888
155	9	180
156	9	568
157	9	135
158	9	360
159	9	379
160	9	478
161	9	289
162	9	234
163	9	117
164	9	126
165	9	667
166	9	144
167	9	199
168	9	469
169	9	900
170	9	559
171	9	450
172	9	225
173	9	577
174	9	270
175	9	388
176	9	333
177	5	140
178	5	159
179	5	456
180	5	690
181	5	230
182	5	258
183	5	357
184	5	780
185	5	249
186	5	799
187	5	447
188	5	348
189	5	339
190	5	889
191	5	168
192	5	113
193	5	366
194	5	122
195	5	177
196	5	267
197	5	500
198	5	555
199	0	190
200	0	569
201	0	145
202	0	460
203	0	280
204	0	235
205	0	578
206	0	370
207	0	244
208	0	299
209	0	479
210	0	334
211	0	488
212	0	389
213	0	136
214	0	668
215	0	118
216	0	677
217	0	127
218	0	226
219	0	550
220	0	000
Sheet2

 

[JAVEDR]

Please post your sample to the forum using XL2BB (link in my signature block below) many members of the forum are unable to download attached files for security reasons so your question is likely to go unanswered as it stands.

Thank you sir followed same.

 

[jasonb75]

It is not clear from that exactly what is required, one thing is clear, it is far from any kind of index and match formula. This is my interpretation of the notes in column C with a lot of guesswork to get this far. You will notice that there are many more results here than in your example, if this is not what is needed then you will need to make your explanation much clearer.

Also you have not added your excel version to your forum profile so I've assumed that you have the latest version and based the suggestion below on that. If you are using an older version then please update your profile to show which version that is.

Book1
	D	E
1		123456
2	1	236,245,335,344,155,146
3	2	246,345,255,336,156,444
4	3	256,346,445,355,166,111
5	4	356,112,266,446,455
6	5	456,113,366,122,555
7	6	123,556,114,466,222
8	7	124,133,115,566,223
9	8	125,134,224,233,116,666
10	9	135,234,126,144,225,333
11	0	145,235,244,334,136,226
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,FILTER($B$1:$B$220,($A$1:$A$220=D2)*ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))

 

[JAVEDR]

It is not clear from that exactly what is required, one thing is clear, it is far from any kind of index and match formula. This is my interpretation of the notes in column C with a lot of guesswork to get this far. You will notice that there are many more results here than in your example, if this is not what is needed then you will need to make your explanation much clearer.

Also you have not added your excel version to your forum profile so I've assumed that you have the latest version and based the suggestion below on that. If you are using an older version then please update your profile to show which version that is.

Book1
	D	E
1		123456
2	1	236,245,335,344,155,146
3	2	246,345,255,336,156,444
4	3	256,346,445,355,166,111
5	4	356,112,266,446,455
6	5	456,113,366,122,555
7	6	123,556,114,466,222
8	7	124,133,115,566,223
9	8	125,134,224,233,116,666
10	9	135,234,126,144,225,333
11	0	145,235,244,334,136,226
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,FILTER($B$1:$B$220,($A$1:$A$220=D2)*ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))

It is not clear from that exactly what is required, one thing is clear, it is far from any kind of index and match formula. This is my interpretation of the notes in column C with a lot of guesswork to get this far. You will notice that there are many more results here than in your example, if this is not what is needed then you will need to make your explanation much clearer.

Also you have not added your excel version to your forum profile so I've assumed that you have the latest version and based the suggestion below on that. If you are using an older version then please update your profile to show which version that is.

Book1
	D	E
1		123456
2	1	236,245,335,344,155,146
3	2	246,345,255,336,156,444
4	3	256,346,445,355,166,111
5	4	356,112,266,446,455
6	5	456,113,366,122,555
7	6	123,556,114,466,222
8	7	124,133,115,566,223
9	8	125,134,224,233,116,666
10	9	135,234,126,144,225,333
11	0	145,235,244,334,136,226
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,FILTER($B$1:$B$220,($A$1:$A$220=D2)*ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))*ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))

Sir your are right - You will notice that there are many more results here than in your example.

Also result which delivered by you is perfect.

While pasting formula I'm getting error "The function is not valid" I'm using excel 2019 version

 

[jasonb75]

I'm using excel 2019 version

Please update your profile so that we can see this information without having to ask every time you post.

The filter function is not valid in excel 2019, I will have a look at an alternative for you later when I have a little more free time.

 

[JAVEDR]

Please update your profile so that we can see this information without having to ask every time you post.

The filter function is not valid in excel 2019, I will have a look at an alternative for you later when I have a little more free time.

Thank you sir for your valuable time I have updated my profile as instructed

 

[jasonb75]

This version of the formula will work with excel 2019, note that the formula must be array confirmed with Ctrl Shift Enter.

Book1
	D	E
1		123456
2	1	236,245,335,344,155,146
3	2	246,345,255,336,156,444
4	3	256,346,445,355,166,111
5	4	356,112,266,446,455
6	5	456,113,366,122,555
7	6	123,556,114,466,222
8	7	124,133,115,566,223
9	8	125,134,224,233,116,666
10	9	135,234,126,144,225,333
11	0	145,235,244,334,136,226
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,IFERROR($B$1:$B$220/($A$1:$A$220=D2)/ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JAVEDR]

This version of the formula will work with excel 2019, note that the formula must be array confirmed with Ctrl Shift Enter.

Book1
	D	E
1		123456
2	1	236,245,335,344,155,146
3	2	246,345,255,336,156,444
4	3	256,346,445,355,166,111
5	4	356,112,266,446,455
6	5	456,113,366,122,555
7	6	123,556,114,466,222
8	7	124,133,115,566,223
9	8	125,134,224,233,116,666
10	9	135,234,126,144,225,333
11	0	145,235,244,334,136,226
Sheet2

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,IFERROR($B$1:$B$220/($A$1:$A$220=D2)/ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

I had tried above formula getting below #NAME error don't know where I'm going wrong--

Book1
	A	B	C	D	E
1					123456
2	1	128		1	#NAME?
3	1	137		2	#NAME?
4	1	236		3	#NAME?
5	1	678		4	#NAME?
6	1	245		5	#NAME?
7	1	290		6	#NAME?
8	1	470		7	#NAME?
9	1	579		8	#NAME?
10	1	380		9	#NAME?
11	1	335		0	#NAME?
12	1	588
13	1	489
14	1	344
15	1	399
16	1	100
17	1	155
18	1	560
19	1	146
20	1	669
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=TEXTJOIN(",",1,IFERROR($B$1:$B$220/($A$1:$A$220=D2)/ISNUMBER(SEARCH(MID($B$1:$B$220,1,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,2,1),$E$1))/ISNUMBER(SEARCH(MID($B$1:$B$220,3,1),$E$1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.