Partial Text search

[12Rev79]

Dear Experts,

I have this formula
=FILTER(FILTER('[RFI Log.xlsx]RFI'!$A:$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!$AG:$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})

that would search exact value from my Cell B1.

I want to be dynamic not just the exact value but also a partial text.

How would I modify my formula I have tried doing ISNUMBER(SEARCH("*"&B1&"*"

but still it's not giving me any values.

Please is anyone is kind to help me figure out.

Thanks in advance.
Rev12

 

[jtakw]

HI,

No way to figure out your situation, please post sample data with expected results.
Check my signature regarding posting via XL2BB, Thank You.

I probably can't help, as I don't have the FILTER function, but it would help other potential helpers for posting samples.

 

[Peter_SSs]

I have this formula
=FILTER(FILTER('[RFI Log.xlsx]RFI'!$A:$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!$AG:$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})

that would search exact value from my Cell B1.

No, your formula already searches for the B1 value as partial text in column AG of the other sheet.

Here is my dummy data for RFI

RFI Log.xlsx
	A	B	C	D	E									N	O	P																	AG
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5									Hdr14	Hdr15	Hdr16																	Hdr33
2	ABCD20	ABCD668	ABCD669	ABCD532	ABCD749									ABCD177	ABCD2	ABCD302																	ABCD578
3	ABCD51	ABCD973	ABCD192	ABCD630	ABCD285									ABCD516	ABCD733	ABCD464																	ABCD323
4	ABCD887	ABCD109	ABCD318	ABCD439	ABCD364									ABCD119	ABCD590	ABCD106																	ABCD951
5	ABCD319	ABCD670	ABCD418	ABCD422	ABCD951									ABCD593	ABCD166	ABCD12																	ABCD296
6	ABCD786	ABCD538	ABCD495	ABCD899	ABCD232									ABCD340	ABCD653	ABCD921																	ABCD187
7	ABCD529	ABCD126	ABCD97	ABCD635	ABCD541									ABCD877	ABCD103	ABCD241																	ABCD364
8	ABCD36	ABCD160	ABCD494	ABCD107	ABCD699									ABCD554	ABCD934	ABCD738																	ABCD951
9	ABCD32	ABCD420	ABCD350	ABCD307	ABCD136									ABCD2	ABCD760	ABCD961																	ABCD387
10	ABCD696	ABCD654	ABCD911	ABCD350	ABCD88									ABCD729	ABCD702	ABCD997																	ABCD209
RFI

.. and my formula sheet with your formula from post #1. As you can see it has identified "CD95" as partial text in rows 4 & 8 and returned the relevant columns from those rows.

12Rev79.xlsm
	B	C	D	E	F	G
1	CD95	ABCD887	ABCD318	ABCD439	ABCD119	ABCD106
2		ABCD36	ABCD494	ABCD107	ABCD554	ABCD738
3
Sheet2

Cell Formulas
Range	Formula
C1:G2	C1	=FILTER(FILTER('[RFI Log.xlsx]RFI'!$A:$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!$AG:$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
Dynamic array formulas.

 

[12Rev79]

No, your formula already searches for the B1 value as partial text in column AG of the other sheet.

Here is my dummy data for RFI

RFI Log.xlsx
	A	B	C	D	E									N	O	P																	AG
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5									Hdr14	Hdr15	Hdr16																	Hdr33
2	ABCD20	ABCD668	ABCD669	ABCD532	ABCD749									ABCD177	ABCD2	ABCD302																	ABCD578
3	ABCD51	ABCD973	ABCD192	ABCD630	ABCD285									ABCD516	ABCD733	ABCD464																	ABCD323
4	ABCD887	ABCD109	ABCD318	ABCD439	ABCD364									ABCD119	ABCD590	ABCD106																	ABCD951
5	ABCD319	ABCD670	ABCD418	ABCD422	ABCD951									ABCD593	ABCD166	ABCD12																	ABCD296
6	ABCD786	ABCD538	ABCD495	ABCD899	ABCD232									ABCD340	ABCD653	ABCD921																	ABCD187
7	ABCD529	ABCD126	ABCD97	ABCD635	ABCD541									ABCD877	ABCD103	ABCD241																	ABCD364
8	ABCD36	ABCD160	ABCD494	ABCD107	ABCD699									ABCD554	ABCD934	ABCD738																	ABCD951
9	ABCD32	ABCD420	ABCD350	ABCD307	ABCD136									ABCD2	ABCD760	ABCD961																	ABCD387
10	ABCD696	ABCD654	ABCD911	ABCD350	ABCD88									ABCD729	ABCD702	ABCD997																	ABCD209
RFI

.. and my formula sheet with your formula from post #1. As you can see it has identified "CD95" as partial text in rows 4 & 8 and returned the relevant columns from those rows.

12Rev79.xlsm
	B	C	D	E	F	G
1	CD95	ABCD887	ABCD318	ABCD439	ABCD119	ABCD106
2		ABCD36	ABCD494	ABCD107	ABCD554	ABCD738
3
Sheet2

Cell Formulas
Range	Formula
C1:G2	C1	=FILTER(FILTER('[RFI Log.xlsx]RFI'!$A:$AG,ISNUMBER(SEARCH(B1,'[RFI Log.xlsx]RFI'!$AG:$AG)),""),{1,0,1,1,0,0,0,0,0,0,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0})
Dynamic array formulas.

Thank you appreciated, yes it works will so far.

 

[Peter_SSs]

You're welcome but I really didn't do anything as your formula already did what you wanted.

 

[12Rev79]

You're welcome but I really didn't do anything as your formula already did what you wanted.

Yes with your comments makes me re examine my formula and I fount out that it works. Thanks a lot.