Filter problem

[sharshra]

When there are multiple items in the cell referred by include parameter, how to get the correct result for the parameter passed?

For example, consider the table below. When I filter for rq 1, it returns d2 only since it has only rq 1. There are other rows which has rq 1 along with other values. I would like the formula to return d2, d4 (d2 has rq 1 only & d4 has rq 7, rq 1). Can the experts in this forum suggest please?

requirement mapping.xlsx
	F
3	d2
table 2

Cell Formulas
Range	Formula
F3	F3	=CHOOSECOLS(FILTER(B3:C17,C3:C17="rq 1"),1)

requirement mapping.xlsx
	B	C
2	d	rq ref
3	d1	rq 5
4	d2	rq 1
5	d3	rq 10
6	d4	rq 7, rq 1
7	d5	rq 6
8	d6	rq 3
9	d7	rq 9, rq 10
10	d8	rq 2
11	d9	rq 3
12	d10	rq 2
13	d11	rq 4, rq 9
14	d12	rq 8
15	d13	rq 4
16	d14	rq 5
17	d15	rq 10, rq 6
table 2

 

[Cubist]

-

 

[hagia_sofia]

A bit complicated but might work:

=LET(
search,"rq 1",
rqref,C3:C17,
d,B3:B17,
array,BYROW(--(search=DROP(REDUCE("",rqref,LAMBDA(a,b,IFERROR(VSTACK(a,TEXTSPLIT(b,", ")),FALSE))),1)),LAMBDA(a,SUM(a))),
FILTER(d,array>0))

 

[Cubist]

Book1
	B	C	D	E	F
2	d	rq ref
3	d1	rq 5			d2
4	d2	rq 1			d4
5	d3	rq 10
6	d4	rq 7, rq 1
7	d5	rq 6
8	d6	rq 3
9	d7	rq 9, rq 10
10	d8	rq 2
11	d9	rq 3
12	d10	rq 2
13	d11	rq 4, rq 9
14	d12	rq 8
15	d13	rq 4
16	d14	rq 5
17	d15	rq 10, rq 6
Sheet10

Cell Formulas
Range	Formula
F3:F4	F3	=LET( x,C3:C17,y,B3:B17, c,LEN(x) - LEN(SUBSTITUTE(x, ",", ""))+1, a,TOCOL(IFS(c>=SEQUENCE(1,MAX(c)),y),2), b,DROP(REDUCE("",x,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,", ")))),1), FILTER(a,b="rq 1") )
Dynamic array formulas.

 

[Phuoc]

Try this:

Book1
	B	C	D	E
1	d	rq ref
2	d1	rq 5		d2
3	d2	rq 1		d4
4	d3	rq 10
5	d4	rq 7, rq 1
6	d5	rq 6
7	d6	rq 3
8	d7	rq 9, rq 10
9	d8	rq 2
10	d9	rq 3
11	d10	rq 2
12	d11	rq 4, rq 9
13	d12	rq 8
14	d13	rq 4
15	d14	rq 5
16	d15	rq 10, rq 6
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=FILTER(B2:B16,ISNUMBER(SEARCH("rq 1,",C2:C16&",")),"")
Dynamic array formulas.

 

[sharshra]

@Phuoc, thanks for the smart formula. That works We are very close. I just need one more thing to be done. Now the formula results in an output in 2 rows - d2 & d4 in 2 rows. Instead, I need an output in a single cell. For example, output should be d2, d4.

 

[Fluff]

You can do that using textjoin like

=TEXTJOIN(", ",,FILTER(B2:B16,ISNUMBER(SEARCH("rq 1,",C2:C16&",")),""))

 

[sharshra]

Perfect, @Fluff . As always, experts in this forum are always there when you need something in Excel . Thanks a ton

 

[Fluff]

Glad we could help & thanks for the feedback.