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

Question

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>)}

However, it only recognizes a match when the word I'm looking for appears in the first row in the list of terms I'm looking for in the cell I'm searching. Is it possible to create a formula that iterates over the full list? And I would prefer that it return more than just TRUE or FALSE. I am aware of how to perform a cell search for a list of terms and output TRUE or FALSE depending on whether or not each word is present in the cell. I want to know the precise word that was found or its location.

Answer 1

If a match is detected, this will return the word, otherwise, it will return an error. The following is what I used for this example.

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 records 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 by first searching the list of words for matches. The position of the discovered word in the list is represented by the greatest value in this array. A negative value is supplied to the INDEX() method and an error is raised if no word is found.

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.