title: Regexmatch to find all string cells that match multiple words
body: <p>I'm using ArrayFormula and FILTER combination to list all cells in a column that contain all of the search term words. I'm using REGEXMATCH rather than QUERY/CONTAINS/LIKE because my FILTER has other criteria that return TRUE/FALSE.</p>
  
  <p>My problem seems to be precedence. So the following regex works in a limited way.</p>
  
  <pre><code>=ArrayFormula(filter(A1:A5,regexmatch(A1:A5,"(?i)^"&amp;"(.*?\bbob\b)(.*?\bcat\b)"&amp;".*$")))
  </code></pre>
  
  <p>It will find Bob and cat but only if Bob precedes cat.</p>
  
  <p>Google sheets fails if I try to use lookahead ?= ie</p>
  
  <pre><code>=ArrayFormula(filter(A1:A5,regexmatch(A1:A5,"(?i)^"&amp;"(?=.*?\bbob\b)(?=.*?\bcat\b)"&amp;".*$")))
  </code></pre>
  
  <p>I don't want to use the '|' alternation in the string (repeat and reverse) as the input words may be many more than two so alternation becomes exponentially more complex.</p>
  
  <p>Here's the test search array (each row is a single cell containing a string)...</p>
  
  <pre><code>Bob ate the dead cat
  The cat ate live bob
  No cat ate live dog
  Bob is dead
  Bob and the cat are alive
  </code></pre>
  
  <p>... and the desired results I'm after.</p>
  
  <pre><code>Bob ate the dead cat
  The cat ate live bob
  Bob and the cat are alive
  </code></pre>
  
  <p>Once I have the regex sorted out, the final solution will be a user input text box where they simply enter the words that must be found in a string ie 'Bob cat'. This input string I think I can unpack into its separate words and concatenate to the above expression, however, if there's a 'best practice' way of doing this I'd like to hear. </p>