Author: Oscar Cronquist Article last updated on November 28, 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 excel 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?

Array Formula in G3:

=LOOKUP(2, 1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)

Change the following in order to add more conditions:

  • cell reference $E$2:$E$3 if you want more conditions
  • the number after the second equal sign =2 to as many conditions you have in the formula
  • {1; 1} to as many conditions you have. For example, 4 conditions - {1; 1; 1; 1}

How to create an array formula

  1. Select cell F2
  2. Type formula in formula bar