I am trying to create a formula that will automatically tell me which crew is working on a particular day and shift. I have tried several things from many different forums but somehow I am not doing something correct.
This worksheet is very large and I do not want to create new tables or additional columns if at all possible.
You can use the facts that your source data has two lines for each date and the second line is always PM to make this easy.
This looks up the date in the table and returns the first Crew for that date. If the shift is PM, it will return the second record for that date.
What you describe can easily be done with a pivot table. Select the data in the first sheet (or turn it into an Excel Table with Insert > Table), then click Insert > Pivot table.
In the Pivot panel, drag Day, Shifts and Crews into the rows pane and the Lines into the Filters pane. On the Design ribbon of the Pivot Tools, set the pivot table to tabular format and repeat all item labels. These two commands are in the Report Layout dropdown.
Turn off Subtotals and Grand totals.
Select a line in the filters drop down above the pivot table. In the screenshot below, the “All lines” value has been selected.