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:

search for multiple text strings in multiple cells and return multiple values

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

Download excel *.xlsx filesearch-for-multiple-strings-in-multiple-cells-part2 v2.xlsx
(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.