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 file
search-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.

Related posts:

Search for multiple text strings in multiple cells and use in data validation in excel

Search for multiple text strings in multiple cells in excel

Search and display all cells that contain all search strings in excel

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Lookup with multiple criteria and display multiple search results using excel formula, part 2