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?

Answer:

Formula in G2:

=INDEX($B$3:$B$13, SMALL(IF(FREQUENCY(IF(ISERROR(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)), "", MATCH(ROW($B$3:$B$13), ROW($B$3:$B$13))), MATCH(ROW($B$3:$B$13), ROW($B$3:$B$13)))>1, MATCH(ROW($B$3:$B$13), ROW($B$3:$B$13)), ""), ROWS($A$1: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 G2
  2. Paste formula to formula bar
  3. Press and hold CTRL + SHIFT
  4. Press Enter
  5. Release all keys

Download excel *.xlsx file
search-for-multiple-strings-in-multiple-cells-part2 v2.xlsx

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.