How do I replace multiple column with OR condition in a where condition by an array?

Question

My prototype here : the sheet where the formula is place in cell B2

I have this query working but the "where" clause is not optimized by an array if it's possible.

=IFERROR(QUERY(F:N, "SELECT F WHERE G CONTAINS '"&A2&"' OR H CONTAINS '"&A2&"' OR I CONTAINS '"&A2&"' OR J CONTAINS '"&A2&"' OR K CONTAINS '"&A2&"' OR L CONTAINS '"&A2&"' OR M CONTAINS '"&A2&"' OR N CONTAINS '"&A2&"'"),"")

Is there a formula to remove all OR clause by an array ?

I tried with no success:

SELECT ArrayFormula(textjoin(", ",TRUE,("Col"&row(indirect("A"&F1&":A"&O1)))))

Answer 1

instead of using OR like:

=IFERROR(QUERY(F:N, 
 "SELECT F 
  WHERE G CONTAINS '"&A2&"' 
     OR H CONTAINS '"&A2&"' 
     OR I CONTAINS '"&A2&"' 
     OR J CONTAINS '"&A2&"' 
     OR K CONTAINS '"&A2&"' 
     OR L CONTAINS '"&A2&"' 
     OR M CONTAINS '"&A2&"' 
     OR N CONTAINS '"&A2&"'"))

you can do:

=IFERROR(QUERY({F:N, FLATTEN(QUERY(TRANSPOSE(F:N),,9^9))}, 
 "select Col1
  where Col10 contains '"&A2&"'", ))