How to check multiple columns for a value in google sheets?-CodePudding

I want to extract the first column value for the rows that any of the other values contain a specific string, I know the hard coded way would look something like this

=QUERY(A3:Q24; "select A where B contains 'No' or where C contains 'No' or where D contains 'No'";0)

However if i want to do this for a lot of columns this doesn't seem as a good way. Is there anything else i can use?

CodePudding user response：

try:

=QUERY({A3:Q24\ FLATTEN(QUERY(TRANSPOSE(B3:Q24);;9^9))}; 
 "select Col1 
  where Col18 contains 'No'"; 0)