Excel: Search for a list of strings within a particular string using array formulas?

16
votes

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
excelsearchexcel-formulafind
This would probably be much simpler if you could use multiple FIND calls: e.g. use one-cell-per-word-searched. Is that an option? Alternatively, you could write a VBA function to handle this. – Rachel Hettinger
the <list of words I want to search for> is an array with one word per cell. Is this what you mean? I don't know VBA, but do you know of any example VBA code that does something similar? – John Thompson
Could whoever downvoted me please provide some feedback? – John Thompson
If you don't know VBA, first try doing a FIND in each cell adjacent to the array of cells you want to search. You'll get one result per word searched and then can use that information however you want. – Rachel Hettinger
oh I see, for each cell I want to search, do a row of FINDs, one per word that I'm searching for. – John Thompson

4 Answers

36
votes

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.

6
votes

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

0
votes
  1. Arange your word list with delimiter which never occures in the words, f.e. |
  2. 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")}
  3. 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)
0
votes

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.