Lookup Formula with 2 criteria to build schedule

[Maglin]

Hello,
I've searched high and low and can not come up with a way to solve this. I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.

I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. All help is appreciated. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.

BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope. Again thank you in advance for all help given. I seem to learn new functions in Excel on a weekly basis.

[JBeaucaire]

Your sample is incomplete and difficult to understand.

1) The names of employees are represented where? A,B,C? Perhaps Name1, Name2,Name3 is more obvious junk names.
2) Your sign in sheet does not demonstrate the desired result. Mock up a solution manually and completely, make sure it's clear what is supposed to be automated and what is referred to when the data is pulled in.

You might need to create several examples of the Sign In sheet to fully demonstrate all the hurdles that have to be overcome.

[Maglin]

Sorry this is the first time I've ever had to ask for help.

I took your suggestion and changed the document and added comments. Along the top of the schedule is numbers that correspond to the date which is one field I want to be able to specify. Then I want to search down that date column for the shift (ie. D) and put the people on that shift into the Assigned Crewmember column. Along with that I would also like to get their qualifications from column B of the schedule and put that in the position Qual (column B) on the Crew Sign in sheet. After rereading it I realized how I didn't make sense. I hope I'm a little more clear now.

[JBeaucaire]

Why are names 1-13 included in the result and not others. Exactly why, for each name?

[JBeaucaire]

For instance, using AE1 and AE2 as the search criteria, I get names 1-11 using a standard multiple-row detection array.

[Maglin]

That was me doing what I do at work and manually putting them into the Sign in Sheet as they are day shift just for a different position all together. So you used:

Please Login or Register  to view this content.

There is more there than I can figure out how it's working. It works beautifully. The few TAC people can be manually input as they are always just 2-3 per shift. Thank you very much for doing this. If it's not to complex would you mind going over how this works or point me to some tuturials I can read up on how to make formula's like this? Again thank you so very much.

Edit: NM. Just Googled Excel multiple-row detection array and found a few examples that are well explained. I was searching for the wrong thing.

[JBeaucaire]

I've marked this thread as SOLVED for you.

Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.