I have a Google Sheets database that I want to query. I want the query to be based on data in columns B, C, and D and return all data if the conditions are met. I have input cells for the search criteria for those three columns, and I'd like the query to do the following:

1. Ignore search criteria where there is no value entered in the search cell
2. Use AND logic where there is a value in a given cell

For example, search criteria in input cells:

• B = "1"
• C = Blank
• D CONTAINS "the"

I want the query to return data where C is anything and B = "1" and D CONTAINS "the".

Another example:

• B = "1"
• C = "E"
• D CONTAINS "the"

I want the query to return data where all three conditions are met

I'd like D to not be null in all cases.

I'm having trouble incorporating the empty criteria into the query. I've tried dynamically using AND/OR depending on whether there's data in a given search cell, but I can't get it to work.

So, I created a crazy-long query to account for all possible combinations with B, C, and D. This works, but I want to incorporate more search criteria, and this equation becomes exponentially more complicated with 4, 5 or more search criteria.

Here is an image of the query input and query results for the first example above (I'm not sure why some of the values in the Name column don't contain "the"). Image of Query Sheet

And here's an image of the database that I'm querying against: Image of Database Sheet

Here is the equation I'm using:

=if(and(B4="",C4=""),query(DATABASE!A1:E,"SELECT * WHERE D contains '"&D4&"' and D IS NOT NULL order by D asc",1),
if(and(B4<>"",C4=""),query(DATABASE!A1:E,"SELECT * WHERE D contains '"&D4&"' and B matches '"&B4&"' and D IS NOT NULL",1),
if(and(B4="",C4<>""),query(DATABASE!A1:E,"SELECT * WHERE D contains '"&D4&"' and C matches '"&C4&"' and D IS NOT NULL",1),
if(and(B4<>"",C4<>""),query(DATABASE!A1:E,"SELECT * WHERE D contains '"&D4&"' and B matches '"&B4&"' and C matches '"&C4&"' and D IS NOT NULL",1),
if(D4="",query(DATABASE!A1:E,"SELECT * WHERE B matches '"&B4&"' "&IF(AND(B4<>"",C4<>""),"AND","OR")&" C matches '"&C4&"' and D IS NOT NULL",
1),FALSE)))))

My ask: I'd like to simplify this equation so that it can be extended to multiple search criteria (more than 3). Thank you for your help!

CodePudding user response：

Try this:

=QUERY(
  DATABASE!A:E,
    "WHERE D IS NOT NULL"
  & IF(B4 = "",, " AND B = " & B4)
  & IF(C4 = "",, " AND C = '" & C4 & "'")
  & IF(D4 = "",, " AND D CONTAINS '" & D4 & "'")
  & " ORDER BY D",
  1
)