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?

Formula in F2:

=INDEX(\$A\$2:\$A\$12, SMALL(IF(FREQUENCY(IF(ISERROR(SEARCH(TRANSPOSE(\$D\$2:\$D\$3), \$A\$2:\$A\$12)), "", MATCH(ROW(\$A\$2:\$A\$12), ROW(\$A\$2:\$A\$12))), MATCH(ROW(\$A\$2:\$A\$12), ROW(\$A\$2:\$A\$12)))>1, MATCH(ROW(\$A\$2:\$A\$12), ROW(\$A\$2:\$A\$12)), ""), ROW(A1)))

You can change the cell reference \$D\$2:\$D\$3 to any size, as long it is referencing a single column.

How to create an array formula

1. Select cell F2
2. Paste formula to formula bar
3. Press and hold CTRL + SHIFT
4. Press Enter
5. Release all keys

search-for-multiple-strings-in-multiple-cells-part2 v2.xlsx
(Excel 97-2003 Workbook *.xls)

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.