Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel :

If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excell to select the range of strings, so that you don't have to select each string "SEARCH($D$3" in the search parameter, as it seems is the case at the moment?

Picture from blog post "Search for multiple text strings in multiple cells in excel"

multiple-strings1

Answer: The formula presented below extracts all records that contain any of the search strings, see picture below. This is not exactly what Jerome asks for but I thought this could be interesting enough.

Jerome asks for all records that contain all search strings. I have to think some more to solve Jeromes question.

search-for-multiple-text-strings-in-multiple-cells-in-excel-part-2

Formula in G2:G12:

=INDEX(List, SMALL(IF(ISERROR(SEARCH(Search_strings, TRANSPOSE(List))), "", TRANSPOSE(ROW(List)-MIN(ROW(List))+1)), ROW(List)-MIN(ROW(List))+1)) + CTRL + SHIFT + ENTER

If more than one search string are found in a cell, duplicates (or more) are created in the search result. (See picture above)

Named ranges
List (A2:A12)
Search_strings (D2:D3) Change this range if you want to include more search strings
What is named ranges?

How to implement array formula to your workbook
Change named ranges.

Download excel example file.
search-for-multiple-strings-in-multiple-cells-part2.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ROW(reference) Returns the rownumber of a reference

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

SMALL(array,k) Returns the k-th smallest row number in this data set.

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.