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

 

[Peter_SSs]

@Leifnier
Welcome to the MrExcel board!
Glad you found it useful. However, do you really have anything like a million values in column C and do you need all 1,048,576 cells in the column populated with a value (most of which I am guessing are zeros)? Using a whole column reference like that makes the formula really slow to respond for me.