Help on a project utilizing formulas, lookups, and sorting

[bh24524]

Hello, I have a project that is rather involved that I'm not sure how to go about doing. This project involves tracking vacations each year for employees in different departments. Currently, we are using an application that uses FoxPro which I have no familiarity with and only one person in our entire company does. Recently, we had a malfunction in the program and needed it fixed ASAP to be able to post vacation charts which are generated from the program, one chart for each dept which is exported to Excel. In each employees name, we will have their dept. code as well as a shift code which designates what shift they are on and what days they have off during the week respectively. Example for the Grocery Shifts - the dept code for a daylight shift is C61C and their are several different shift codes for it to designate the days off. There are many different codes, I think 44 in total right now, but I'll just list 3 of them for simplicity sake: "3", "7", and "R". So you could have C61C - 3, C61C - 7, and C61C - R.

I am ultimately looking to see if this function in the program we are currently using could ultimately work in Excel. This way should the person who has the knowledge of the program leave the company, we wouldn't be stuck if the program malfunctioned again. What I picture is having a chart template on its own tab that has a drop-down menu for the depts. and the shift codes. Cells A11 thru A42 and Cells B11 thru B42 would be the cells that contain Employee numbers and names respectively. These numbers and names would be looked up from a different tab and would actually change depending on what Dept. Code and Shift code is chosen from the Shift Drop-down menu that I mentioned above. Also, I would want it to automatically sort the populated names by seniority date which is actually contained in cells C11 thru C42 - Now those dates get tricky because we haev two groups of employees so those dates are actually looked up from two different tabs, not one.

I know how to do Index/match lookups when an employee number is typed in to return that specific value but not how to do lookups where when you type a code that fits multiple employees that it would bring multiple names up. So this would be my first hurdle. Is something like this possible to do? I'll give more specific info as to the other tabs but just first want to find out if it's doable.

 

[bh24524]

Okay, so I did a little test on a separate sheet with a few additional dept codes, and here's what I have so far:

Book1
	A	B	C	D	E	F	G	H	I
1	john 1	C61C	A	123			Dept	Code
2	john 2	C61C	3	124			C61C	3	124
3	john 3	C65L	1	125					127
4	john 4	C65D	M	126					132
5	john 5	C61C	3	127					137
6	john 6	C65D	M	128					142
7	john 7	C61C	A	129					147
8	john 8	C65D	M	130
9	john 9	C65L	1	131
10	john 10	C61C	3	132
11	john 11	C65D	M	133
12	john 12	C65L	1	134
13	john 13	C61C	A	135
14	john 14	C67G	4	136
15	john 15	C61C	3	137
16	john 16	C65L	1	138
17	john 17	C61C	A	139
18	john 18	C67G	4	140
19	john 19	C65L	1	141
20	john 20	C61C	3	142
21	john 21	C65D	M	143
22	john 22	C61C	A	144
23	john 23	C65D	M	145
24	john 24	C65L	1	146
25	john 25	C65L	3	147
26	john 26	C65D	M	148
27	john 27	C65L	1	149
28	john 28	C61C	A	150
Sheet1

Cell Formulas
Range	Formula
I2:I28	I2	=IFERROR(INDEX($D$1:$D$200, SMALL(IF(($H$2=$C$1:$C$200), MATCH(ROW($C$1:$C$200), ROW($C$1:$C$200)), ""),ROWS($A$1:A1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

With this formula, I can get it to yield the results of all occurrences of a "3". What I am looking to do if possible is have it yield all the results that fit C61C and 3. If I need to I can just use a macro to sort the dates that I will eventually put in so thats no problem. But for this example, there is a C65L dept. with a 3 that should not be counted. What formula could I use for this to capture those 2 criteria in G2 and H2?