Author: Oscar Cronquist Article last updated on February 05, 2018

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 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

search-for-multiple-strings-in-multiple-cells-part2 v2.xlsx

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.