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

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

If you have better answer, please add a comment about this, thank you!

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

Resolved: EntityFramework creates/runs migrations using parameterless DataContext instance

Resolved: Visual Studio 2022 crashes when using breakpoints

Resolved: How to get Union type from an array of objects in Flow?

Leave A Reply