Reputation: 1
How to Check a Cell for Multiple Text Strings
I am trying to find a way to search a list (“Search Terms” in screenshot) and see if that text string contains both word 1 (D2) and word 2 (E2). The order in which “word 1” or “word 2” in the search doesn’t matter. I would like to know if they are both present in the “search term”.
I found this formula: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
This very close to what I need. However, it designed to search 1 list.
Is there a way to create an "and" function in the formula above? So that the formula searches both “word 1" and “word 2" lists and returns true if both words are present in the keyword. Returns a false value if 1 or zero of the 2 words is present in the search term.
Upvotes: 0
Views: 3018
Answers (1)
Reputation: 50008
Perhaps like this:
=SUMPRODUCT(--ISNUMBER(SEARCH({"garden","watering"},B5)))=2
If you want to add more criteria or quickly modify the criteria, perhaps use a list, like so:
=SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$3,B5)))=COUNTA($C$1:$C3)
Upvotes: 1
Related Questions
- EXCEL Search of multiple possible strings of text
- EXCEL: Check for Multiple Values
- Excel: compare two cells containing strings and return TRUE if multiple words match
- Check cell for multiple strings and return specific values
- Excel check cell for string match against multiple other cells
- Check if a value is in a string
- Check if one of multiple values is present in a column
- EXCEL - IF multiple strings in a range of cells
- Excel: Search if multiple texts exist in a cell
- A better way to check whether multiple strings occur in a given cell