Forum Discussion
NOVICE USER - How to find a cell using multiple search terms?
I'm a designer in my workplace and given that the server has files all over, I would find myself on wild goose chases looking for files that people forgot/don't know where they could be. I ended up c...
=ISNUMBER(SEARCH("3x",A2))*ISNUMBER(SEARCH("Sticker",A2))Maybe with this formula which is in cell B2 in the example. It returns "1" if both search terms are found in the cell.
=SUMPRODUCT((ISNUMBER(SEARCH("3x",A2:A8))*ISNUMBER(SEARCH("Sticker",A2:A8))))This formula is in cell C2 in the example and it returns the count of cells that contain "3x" and "Sticker".
If you have Excel 2019 or later you can apply the FILTER function to filter the cells that contain "3x" and "Sticker".
=FILTER(A2:A8,(ISNUMBER(SEARCH("3x",A2:A8)))*(ISNUMBER(SEARCH("Sticker",A2:A8))))