Google Sheets multiple query matching words and numbers

0
votes

I need to query a sheet in Google Sheets to find matches for multiple words where values are greater than a specified number.

For example, I want to find all words containing 'garden' or 'gate' and the value is greater than '30' in a sheet that looks something like this:

enter image description here

I have this formula but it's not working:

=QUERY(Sheet1!A:B,"select * where A contains 'garden' OR A contains 'gate' AND B>30 ")
2
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

2 Answers

2
votes
=QUERY(Sheet1!A:B, 
 "where B > 30 
    and (A contains 'garden' 
     or  A contains 'gate')", 0)

0

=QUERY(Sheet1!A:B, 
 "where A matches '.*garden.*|.*gate.*' 
    and B > 30", 0)
1
votes

Please try:

=QUERY(Sheet1!A:B,"where  B > 30 and (A like '%garden%' OR A like '%gate%')")