Attempting to Search one cells number values against an numbered ID list

[jeremyweck]

For starters this cannot be in VBA the intended users are not familiar enough with VBA to maintain the document. That being said im 90% of the way there. I have one cell with 2 or 3 number values between 1 and 35 and another cell returning all the values that are inside that cell. My only problem is that it is treating multiple digit numbers as individual integers. 1 and 32 is returning values for 1, 2, 3 and 32 and all i want is 1 and 32. I honestly think im right on the edge of it but i just cant seem to get my brain there.

My current formula

=TEXTJOIN(", ", TRUE, IF(COUNTIF(C2, "*"&$A$2:$A$35&"*"), $B$2:$B$35, ""))

 

[StephenCrump]

Welcome to the Forum!

Here's one way:

	A	B	C	D
1
2	1	A	1,32	A,gg
3	2	B	2,3,5,34	B,C,F,END
4	3	C	6	G
5	4	E
6	5	F
7	6	G
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
26	25
27	26
28	27
29	28
30	29
31	30
32	31
33	32	gg
34	33
35	34	END
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=TEXTJOIN(",",,INDEX(B$2:B$35,FILTERXML("<x><y>"&SUBSTITUTE(C2,",","</y><y>")&"</y></x>","//y")))

You've shown

C2: 1,'32

If quote marks are the only potential superfluous characters (?) you could add a simple SUBSTITUTE within this formula to strip them out.

 

[jeremyweck]

Thank you. That does appear to get the job done. This is the first i have worked with FilterXML do you happen to know if its compatible with older excel versions and could i use an index, match variation to achieve the same result?

 

[Fluff]

The Filterxml function is available from xl 2013, however Textjoin is only available from xl 2019