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  lt list of words I want to search for gt   lt cell I want to search gt      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

User · Answer

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

User · Answer

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

User · Answer

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  amp     to the cell value tested  works well with array formulas  cell G3 could contain    SUM FIND  A 1  A 100 amp     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

User · Answer

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

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

Examples related to excel

Examples related to search

Examples related to excel-formula

Examples related to find