Google sheets formula or query for employee schedule

Question

I am currently using google sheets for employee scheduling. On the "Schedule" sheet Row 1 starting from column B I have the employee names listed across the top and column A starting from row 2 has dates. I place an "X" in the cell corresponding to the date and name of the employee if they are on duty, otherwise "OFF", or "LEAVE", are used to indicate they are not on duty. It's a simple schedule since there's only one shift and all employees are full time.

I want to create a new tab "Daily Plan" where I can generate a list of all employees that are on duty on a particular day, based upon the date in cell A2 of the daily plan tab. Basically I'm looking for a formula that will look at the date in Cell A2 of Daily Plan, match it to the date in A2:A of the "Schedule Sheet" find the all the columns with "X" in that row, and return the names contained in Row 1 of those columns. I've tried a combination of if(and( and index(match functions, but can't seem to get it to work correctly. I know there has to be a logical way to do this or perhaps a cleaner way of laying out the schedule.

I've created a sample sheet for better clarification on what I'm trying to do here: https://docs.google.com/spreadsheets/d/1F54iltBk2t6A79jOYp-un7ZE5uN7JrUHmu_LILfvwe4/edit#gid=0

Any guidance on how to get this would be really appreciated.

Answer 1

Use filter(), like this:

=join( 
  ", ", 
  filter( 
    Schedule!B1:Z1, 
    "X" 
    = 
    filter(Schedule!B2:Z, Schedule!A2:A = A2) 
  ) 
)

See your sample spreadsheet.