Search keyword in range of cells, then copy entire cell to another column

Hi there,

I am trying to come up with a function that will allow me to do the same thing as the find command but automatically in function form. To do this I would like to be able to search for a keyword within a range of cells, and then copy the cells containing that keyword to a separate column. For example, if one column was a list like "red fish, blue fish, red leader, clifford the big red dog, blue meanie" and the keyword I was searching for was "blue" then the new seperate column would read "Blue fish, blue meanie". Any help you can give would be greatly appreciated.

Tried out your formula but I can't get it to work for my actual data set. Let's say my phrases are in AT1:AT91007 and the word blue is in F1. I wrote =IFERROR(INDEX($AT$1:$AT$91007, AGGREGATE(15,6,INDEX(--(SEARCH($F$1,$AT$1:$AT$91007)/SEARCH($F$1,$AT$1:$AT$91007))*ROW($AT$1:$AT$91007),),ROWS($AT$1:$AT$91007))),"") however the cells remain blank.

Hello
Suppose the data in "A4: A8" and keyword in B2
Build in "C4:C8" the following matrix formula

Formula:

Please Login or Register  to view this content.

put on B4, valid in the range, "B4: B8" the formula

Formula:

Please Login or Register  to view this content.

I send a file.


Search.xlsx