I have a roster that I am trying to automate. I would like to be able to
place the date in a cell and in another cell return the correct crew for that
date. This is complicated by the fact we have two shifts per day, 3 crews
that alternate between these shifts and each date corresponds with a
different crew roster. This may explain it a bit better:

each line corresponds to a month, but the dates in column one do not start
with the 1st day of the month - dates correlate to days of the week
(perpetual calendar)

week crew mon tue wed
1 a ngt day day
b day ngt off (blank cell)
c off off ngt

2 a d off n
b n n off
c off d d

ditto for week 3

mon tues wed thurs
week 1 10 Jun 11 Jun 12 Jun 13 Jun
week 2 8 Jul 9 Jul 10 Jul 1 Jul
week 3 5 Aug 6 Aug 7 Aug 8 Aug

I would like to select a date in another worksheet, and in another cell have
the date on the above example recognised and return in this other cell the
corresponding crew and shift that they are on. eg. for 9 July the answer I
need is "b crew ngt shift"

Can anyone help please - I've tried lots of combos of match, index and
lookup and can't seem to get it to work?