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]

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)

CodePudding user response：

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