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

user_19349937 0
Viewed 32

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)))))
formula flatten transpose google-sheets arrays
2 Answers

It's not necessarily more efficient if the case is simple, but if it's a complicated or long code you could design flags that resemble certain situations and just compare the array of the wanted outcome to the array that you actually get.

user_193502640

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&"'", ))
user_56326290
Related