Lookup available people for a given week and day in google sheets-CodePudding

So I am looking for a bit of advise on how to solve my task/issue here!

I have a tab called Attendance where there is a list of people and coresponding dates / weeks. I have another tab called Raid Where I want to see the list of people who are able to attend for Wedensday and another list of people who are able to attend on Thursday, for that coresponding week which are specified in the raid tab.

If someone can not attend, then the name will be marked in the Attendance tab with either a 0 or x or something

I will attach an example sheet here: https://docs.google.com/spreadsheets/d/12Qg3PpwsRZK_ImviNb2u1ibauD8ro4u6Y6vJymnQx30/edit#gid=1332938469

I hope someone with some sheet magic and help me with a solution on how to achieve this!

CodePudding user response：

wednesday:

=QUERY(FLATTEN(FILTER(Attendance!B6:22&Attendance!A6:A22&Attendance!B6:22, 
 ISOWEEKNUM(Attendance!B5:5)=REGEXEXTRACT(D1, "\d ")*1, 
 WEEKDAY(Attendance!B5:5, 2)=3)), 
 "where not Col1 matches 'x.*x|0.*0'", )

thursday:

=QUERY(FLATTEN(FILTER(Attendance!B6:22&Attendance!A6:A22&Attendance!B6:22, 
 ISOWEEKNUM(Attendance!B5:5)=REGEXEXTRACT(D1, "\d ")*1, 
 WEEKDAY(Attendance!B5:5, 2)=4)), 
 "where not Col1 matches 'x.*x|0.*0'", )