1

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!

1 Answer 1

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)
2
  • Thanks doubleunary! Is there a way to return records if Team = East OR West? For example if cell A1 is East, West.
    – Elaine
    Jul 13, 2023 at 20:50
  • Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead. Jul 13, 2023 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.