0

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 where Team equals one of the values listed in cell A1 in the format West, East.

Additionally, the value in Date column should be between the date in cell A2 (beginning, inclusive) and cell A3 (ending, inclusive.)

The results should be sorted ascending by date.

1
  • 1
    Please edit your question to include the formulas you have tried and what didn't work for you. Also, include a sample of the output you expect.
    – Blindspots
    Jul 14, 2023 at 1:55

1 Answer 1

2

Use the matches keyword together with a regular expression, like this:

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

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.