SEARCH problem?

[sharshra]

This has reference to one of my post (Filter problem) which was solved. However, I´m facing an issue. I think the formula searches for first digit only. If there is more than 1 digit number, it will search & return true if one of the digits match the search text. I suspect SEARCH is not working properly. I´m sure I must have made silly mistake but couldn´t figure out. Can the experts in this forum help please?

Consider the following example. Table 1 has t id & used r id. Table 2 has r id & t ref. I have to find out which item under r id in table 2 is called under used r id in table 1.
The formula works well to some extent. But when a number is repeated in any of the digits, it is returning incorrect result.
Let´s look into few results -
- rq id 9 is not used in table 1, but formula returns 3, 8 since it has 89. Formula detects 9 in 89 & returns true.
- rq id 1 is used only in t id 2 in table 1, but formula returns 1, 2, 6 since 1 & 6 has 11 & returns true.

Table 1 -

Book1
	B	C	D
2	t id	desc	used r id
3	1	fdsfds	11
4	2	fdfds	1,11,2
5	3	er	89
6	4	ere	32
7	5	vvafd	3,5
8	6	wqw41	73,32,11
9	7	yiy8	5,55
10	8	65jg	89
11	9	ere	2
12	10	3y4y	3
Sheet1

Table 2 -

Book1
	B	C	D
2	rq id	rq	t ref
3	1	vcv	1, 2, 6
4	11	41d	1, 2, 6
5	2	kukyu	2, 4, 6, 9
6	32	yutry	4, 6
7	3	4fv	5, 6, 10
8	73	urtr	6
9	5	c<c	5, 7
10	55	nbmb	7
11	9	eqq	3, 8
12	89	796k	3, 8
Sheet 2

Cell Formulas
Range	Formula
D3:D12	D3	=LET( x,Table2[t id], y,Table2[used r id], IFERROR(TEXTJOIN(", ",,(FILTER(x,ISNUMBER(SEARCH([@[rq id]]&",",y & ","))))),"no value"))

Expected result - Incorrect results are highlighted in orange cell color.

Book1
	G	H
1	expected values in t ref
2	rq id	t ref
3	1	2
4	11	1,2,6
5	2	2,9
6	32	4,6
7	3	5,10
8	73	6
9	5	5,7
10	55	7
11	9	no value
12	89	3,8
Sheet 2

 

[Fluff]

Looks like you link to the wrong thread, but try

=LET(x,Table2[t id],y,Table2[used r id],IFERROR(TEXTJOIN(", ",,(FILTER(x,ISNUMBER(SEARCH(","&[@[rq id]]&",",","&y & ","))))),"no value"))

 

[sharshra]

That´s great. Thanks a lot, @Fluff. It almost solved my problem. But one more hurdle
When there is a gap between numbers in used r id field in Table2, it does not return correct values. How can this be fixed?

For example, consider the following tables.
Table 1 - Note that t id 7 has used r id of 5, 55.

search problem.xlsx
	B	C	D
2	t id	desc	used r id
3	1	fdsfds	11
4	2	fdfds	1,11,2
5	3	er	89
6	4	ere	2,3
7	5	vvafd	2,3
8	6	wqw41	73,32,11
9	7	yiy8	5, 55
10	8	65jg	89
11	9	ere	2,3
12	10	3y4y	3
Sheet1

Table 2 - Note that 55 is not returned since there is a gap between 5 & 55 in table 1 used r id field. if I remove the space between 5 & 55, it works correctly.

search problem.xlsx
	B	C	D
2	rq id	rq	t ref
3	1	vcv	2
4	11	41d	1, 2, 6
5	2	kukyu	2, 4, 5, 9
6	32	yutry	6
7	3	4fv	4, 5, 9, 10
8	73	urtr	6
9	5	c<c	7
10	55	nbmb	no value
11	9	eqq	no value
12	89	796k	3, 8
Sheet 2

Cell Formulas
Range	Formula
D3:D12	D3	=LET(x,Table2[t id], y,Table2[used r id], IFERROR(TEXTJOIN(", ",,(FILTER(x,ISNUMBER(SEARCH(","&[@[rq id]]&",",","&y & ","))))),"no value"))

 

[sharshra]

Hello Experts, any solution to this problem? We are almost there except one small glitch.

 

[Cubist]

Try:

=LET(
x,Table2[t id],
y,SUBSTITUTE(Table2[used r id],", ",","),
TEXTJOIN(", ",,(FILTER(x,ISNUMBER(SEARCH(","&[@[rq id]]&",",","&y & ",")),"no value"))))

 

[sharshra]

Thanks, @Cubist. It works when there is one space It is good for now.

If there are more than one spaces, it does not work . How can the formula be made more robust to handle any number of spaces?

 

[Cubist]

Remove all the spaces.

=LET(
x,Table2[t id],
y,SUBSTITUTE(Table2[used r id]," ",""),
TEXTJOIN(", ",,(FILTER(x,ISNUMBER(SEARCH(","&[@[rq id]]&",",","&y & ",")),"no value"))))

 

[sharshra]

Great!!! Thank you very much, @Cubist, @Fluff. It fixed my problem You are of great help. I just love this forum