Excel: Search for a List of Strings Within a Particular String Using Array Formulas?
I Want to Search a Cell for a List of Words. I Thought This Would Work as an Array Formula: {=Find(,)} but It Only Finds a Match When a Word That's in the Cell...
I want to search a cell for a list of words. I thought this would work as an array formula:
{=FIND(<list of words I want to search for>,<cell I want to search>)}
But it only finds a match when a word that's in the cell I'm searching sits in the first row of the list of words I'm searching for. Is there any way to write a formula that looks through the entire list? And I'd prefer if it didn't just return TRUE/FALSE. I know how to search cell for a list of words and return TRUE/FALSE based on whether or not a word in the list exists in the cell. I want to actually know which word was found, or its position.
4 Answers
This will return the matching word or an error if no match is found. For this example I used the following.
List of words to search for: G1:G7
Cell to search in: A1
=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))
Enter as an array formula by pressing Ctrl+Shift+Enter.
This formula works by first looking through the list of words to find matches, then recording the position of the word in the list as a positive value if it is found or as a negative value if it is not found. The largest value from this array is the position of the found word in the list. If no word is found, a negative value is passed into the INDEX() function, throwing an error.
To return the row number of a matching word, you can use the following:
=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))
This also must be entered as an array formula by pressing Ctrl+Shift+Enter. It will return -1 if no match is found.
Adding this answer for people like me for whom a TRUE/FALSE answer is perfectly acceptable
OR(IF(ISNUMBER(SEARCH($G$1:$G$7,A1)),TRUE,FALSE))
or case-sensitive
OR(IF(ISNUMBER(FIND($G$1:$G$7,A1)),TRUE,FALSE))
Where the range for the search terms is G1:G7
Remember to press CTRL+SHIFT+ENTER
- Arange your word list with delimiter which never occures in the words, f.e. |
- swap arguments in find call - we want search if cell value is matching one of the words in pattern string
{=FIND("cell I want to search","list of words I want to search for")} - if the patterns are similar, there is a risk of getting more results than wanted, we restrict just correct results via adding &"|" to the cell value tested (works well with array formulas)
cell G3 could contain:
{=SUM(FIND($A$1:$A$100&"|";A3))}this ensures spreadsheet will compare strings like "cellvlaue|" againts "pattern1|", "pattern2|" etc. which sorts out conflicts like pattern1="newly added", pattern2="added" (sum of all cells matching "added" would be too high, including the target values for cells matching "newly added", which would be a logical error)
In case others may find this useful: I found that by adding an initial empty cell to my list of search terms, a zero value will be returned instead of error.
={INDEX(SearchTerms!$A$1:$A$38,MAX(IF(ISERROR(SEARCH(SearchTerms!$A$1:$A$38,SearchCell)),0,1)*((SearchTerms!$B$1:$B$38)+1)))}
NB: Column A has the search terms, B is the row number index.
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
