Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
challenge! - match/index/lookup with multiple criteria
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
challenge! - match/index/lookup with multiple criteria
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 "laststraw" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
challenge! - match/index/lookup with multiple criteria
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 "T. Valko" wrote in message ... 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 "laststraw" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Add a criteria to an Index and Match formula | Excel Discussion (Misc queries) | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) | |||
lookup/index/match - help! | Excel Discussion (Misc queries) |