In Google Sheets how do I query another sheet for a column matching a particular date?

Question

I have two sheets named 'MAR 2022' and 'Roster'. I am wanting to query the Roster sheet and display the header and value that matches a date value in another cell in the MAR 2022 sheet.

QUERY('Roster'!,"select A, B where A = DATE '"&TEXT('MAR 2022'!$A$2,"yyy-MM-dd")&"' ",1)

In the Roster sheet, I have a Date column and headers for activities "Laundry", "Clean Floors", with the name of person beneath. Each date has its own row. In the MAR 2022 I want to basically be a calendar that shows for each day and who is responsible for the activity.

[Roster Sheet] https://i.sstatic.net/KNsfH.png

[MAR 2022 Sheet] https://i.sstatic.net/1TQun.png

Any ideas what I'm doing wrong?

Answer 1

try with row#2 as date formatted dd

=query((arrayformula(split(flatten(Roster!$B$1:$D$1&": "&Roster!$B$2:$D&"~"&Roster!$A$2:$A),"~"))),"select Col1 where Col2="&A2&" ")

and drag to the right

Answer 2

Hmmm, there are likely other ways to solve this but using query I would suggest changing how the source table is laid out since query does not provide for concatenation of 2 columns (Ex. you want to show the duty and person's name combined, Laundry: John)

I was able to accomplish by changing your roster source table as follows:

The March tab looks like the following:

The formula for the days of the week is:

=text(A2, "ddd")

The date functions in A2 is:

=transpose(Roster!A2:A)

The QUERY() functions in A2 thru E2:

=query(Roster!$F$1:$H, "SELECT G WHERE F = DATE '"&TEXT(A2,"yyyy-mm-dd")&"' LABEL G ''", 1)