challenge! - match/index/lookup with multiple criteria

[laststraw]

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?

 

[T. Valko]

I can't see how you arrive at this: for 9 July the answer I need is "b crew
ngt shift"

I'd need to see the actual data. If you want to send a copy of the file to
me I'll take a look. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

I don't have Excel 2007 so don't send *.xlsx files!

Biff

 

[T. Valko]

Wow!

This one was complicated! The resulting formula wasn't too bad but trying to
figure out the relationship of all the data took a while.

However, problem solved. It's too complicated to even attempt to describe
the situation. This could not have been done without seeing the file
firsthand.

Biff