Google Sheets QUERY function to return records with multiple filter criteria based on cell reference

Question

I have a dataset on Google Sheets similar to below.

Name	Team	Account	Date
Jennifer	East	Meta	5/1/2023
Steve	East	Alphabet	4/1/2023
Claire	West	Google	4/23/2023
Roger	West	Apple	6/23/2023
Bob	West	Walmart	5/12/2023

I am trying to use the QUERY function to return columns Name, Account and Date sorted Ascending by date. On another sheet with filter criteria, I want to reference cell A1 where Team = "West" and date range is referenced to cell A2 and A3 with a beginning (A2) and ending (A3) date range.

Any help would be appreciated!

Answer 1

Build the query statement using text string concatenation, like this:

=query( 
  Sheet1!A1:D, 
  "select A, C, D 
   where B = '" & A1 & "' 
   and date " & text(A2, "'yyyy-MM-dd'") & " <= toDate(D) 
   and toDate(D) <= date " & text(A2, "'yyyy-MM-dd'") & " 
   order by D asc", 
  1 
)

For easier debugging, you can put this in another cell, say A4, to see what the query statement looks like:

= "select A, C, D 
   where B = '" & A1 & "' 
   and date " & text(A2, "'yyyy-MM-dd'") & " <= toDate(D) 
   and toDate(D) <= date " & text(A2, "'yyyy-MM-dd'") & " 
   order by D asc"

You can also use that query statement directly in the query() formula, like this:

=query(Sheet1!A1:D, A4, 1)