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

John Peter Thompson Garcés picture John Peter Thompson Garcés · Nov 29, 2011 · Viewed 166.4k times · Source

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.

Answer

Excellll picture Excellll · Nov 29, 2011

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.