Daily roster from calendar

[marjen96]

I am trying to make a spreadsheet with two sheets. One sheet will be a yearly calendar with the date at the top row and employee names along the left column. Each date column will list locations where that each employee will be working. There will be duplicates in this column as sometimes more than one employee will be assigned at the same location. The other sheet will be a daily list of assignments. The daily will be look like a form that will be printed and will have the locations listed on the left column, and blank cells where I need the formula to be. So I need a formula that will check the date in a cell at the top of the daily sheet, look for that date at the top of the calendar, then search down that column and look for an instance of a location and return the name in the left name column. All without getting duplicates.

I have this formula, which works, but it does not meet all my needs.

{=IF(ISERROR(INDEX(Sheet1!$B$1:$B$29,SMALL(IF($L$7=Sheet1!$D$5:$D$14,ROW(Sheet1!$D$5:$D$14)),ROW(Sheet1!1:1)),1)
),"",INDEX(Sheet1!$B$1:$B$29,SMALL(IF($L$7=Sheet1!$D$5:$D$14,ROW(Sheet1!$D$5:$D$14)),ROW(Sheet1!1:1)),1))}

This one looks for the instance of a work location (reference cell outside the print area) and does not return duplicates (2:2, 3:3, etc), but it only searches down the specified column. I need it to look down the column that I type into the date cell on the daily sheet. Thereby only needing to change the date to generate any given daily lineup from the calendar. I hope that makes sense, and thank you for your help in advance.