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.


1 Answer 1


Use filter(), like this:

  ", ", 
    filter(Schedule!B2:Z, Schedule!A2:A = A2) 

See your sample spreadsheet.

  • Thank you for the suggestion. I think my original description did not fully describe the layout of the schedule tab and daily plan tab. I've edited my original post to include a sample layout linked here: docs.google.com/spreadsheets/d/… Jan 9 at 9:06
  • Edited the answer per your new requirements. Jan 9 at 9:20
  • That’s exactly what I was looking for! Thank you so much! Jan 9 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.