Filter, isnumber, search a range of criteria

[shakethingsup]

Hi, I'm good with filter, isnumber, search when there is one criteria to search for.

Now I have a range instead of one piece of text to search for.

My original formula for one criteria was in cell D1 with my search criteria in C1:
=Filter(A:A,isnumber(search(C1,B:B)))

Now I have search criteria in C1 and C2:
- I saw a sumproduct formula but that seems to only search one cell at a time
=SUMPRODUCT(--ISNUMBER(SEARCH(C1:C2,B1)))>0

How do I combine all of this together? Filter + isnumber + search + range of criteria to search? I'm trying to search the forums but am missing something.

Thank you in advance,
Mayank

 

[Fluff]

Try

=Filter(A:A,(isnumber(search(C1,B:B)))+(isnumber(search(C2,B:B))))

 

[shakethingsup]

Thank you for responding! oh you have to do each one individually eh? that would be a pain for more than 2 or 3.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

oh you have to do each one individually eh? that would be a pain for more than 2 or 3.

Would something like one of these be any use to you? Column E searches for the text strings, column G searches for whole words only.
The column C range of things to look for can be as large as you like.

23 05 09.xlsm
	A	B	C	D	E	F	G
1	Data	Text	cat
2	Data 1	dog	dog		Data 1		Data 1
3	Data 2	scatter	rat		Data 2		Data 5
4	Data 3	pig			Data 5
5	Data 4	horse			Data 6
6	Data 5	grey rat
7	Data 6	rather not
Filter multiple values

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(A2:A7,BYROW(B2:B7,LAMBDA(rw,COUNT(SEARCH(C1:C3,rw)))))
G2:G3	G2	=FILTER(A2:A7,BYROW(B2:B7,LAMBDA(rw,COUNT(SEARCH(" "&C1:C3&" "," "&rw&" ")))))
Dynamic array formulas.

 

[shakethingsup]

That worked ridiculously well. I'm going to try and learn this lambda function and how you incorporated 'byrow', 'count'. Brain is still thinking! But thank you!

 

[Peter_SSs]

You're welcome. Glad to contribute.

 

[Biz]

Hey Peter,
The formula is awesome. Where did you create parameter name "rw"?

Kind Regards

Biz

 

[Peter_SSs]

Where did you create parameter name "rw"?

Not sure exactly what you are asking. I just used the syntax as described in the Help of BYROW and the examples there.

 

[Biz]

Hi Peter,

After reviewing the BYRow function, which retrieves all rows and takes a lambda parameter named 'rw', I understand that the 'rw' parameter is used to represent the items that result from the ByRow operation.

Is my understanding correct? Also, I'm curious to know how you learned these amazing formulas.

Kind regards,

Biz