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?

Now I finally have an answer.

Formula in F2:

=SMALL(IF(FREQUENCY(IF(ISERROR(SEARCH(TRANSPOSE(Search_strings), List)), "", ROW(List)-MIN(ROW(List))+1), ROW(List)-MIN(ROW(List))+1)=ROWS(Search_strings), ROW(List), ""), ROW()-1) + CTRL + SHIFT + ENTER copied down as far as necessary.

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 and display all cells that contain all search strings.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

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.

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.

Related posts:

1. Search for multiple text strings in multiple cells and use in data validation in excel
2. Lookup with multiple criteria and display multiple search results using excel formula, part 4
3. Search for a cell in a table and then display the column title in excel
4. Filter unique distinct values where adjacent cells contain search string in excel
5. Vlookup with 2 or more lookup criteria and return multiple matches in excel
6. Convert date ranges into dates in excel
7. Find empty hours in a weekly schedule in excel
8. Count unique distinct text values in a range in excel
9. Filter duplicate values in a range using “contain” condition in excel