CODEWITHSUNDEEP
excelsearch
Mordi
Mordi

Reputation: 35

Excel 2013: Using the Search()-function through a table column?

I'm trying to search through a table column and use the Search()-function to find out if any of the words in the column appear in a given cell.

Image

As you can see here, the formula returns FALSE, even though "Chicken" is in the list. If I move "Chicken" to the top in the column, it will return true. This leads me to believe that the formula only looks at the first item in the column.

Upon entering the formula, I used the CTRL+SHIFT+ENTER keys to make sure it's an array formula.

Does anyone know why it doesn't look through each of the rows in the column?

Edit: I guess the formula stops when it gets any value (even if that value is false)? Maybe there is a function for searching until true?

Upvotes: 1

Views: 68

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Try this non CSE formula:

=IF(SUMPRODUCT((ISNUMBER(SEARCH(Table1[Search words],A1)))*1)>0,TRUE,FALSE)

The reason is that without Sum() or Sumproduct, it is only returning the first iteration.

enter image description here

Upvotes: 3

Related Questions