Match multiple criteria returns a specific result

[bruce24444]

I am trying to build a vacation scheduler for a staff of approx. 100 staff and most of it works fine but I can't figure out one last piece.

What I have is a dashboard, a staff list, a vacation list and then a DOR (day of rest) list.

The dashboard has each employees name in B9:B100
Dates are displayed in C6:AG6

The staff list table has a list of all employees [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]EMP_LIST[Employee] (populates the employee list on the dashboard) and their corresponding DOR Group number (1-6) [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]EMP_LIST[DOR Group][/FONT][/FONT]<strike></strike>

DOR List = Our staff get an additional day off

every 2rd week​

. IE: Every 2rd Monday or Friday. To make sure we can still maintain coverage, everyone is divided into 4 groups.

To manage this I have created a table with heading for each group (1-4) and a label column which is "D" and the table contains a list the days which will correspond with their DOR. I named this table: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I_DOR_ARRAY

So this is where I need help.

On the dashboard: Angie (B9) has a DOR on October 4th (G6). Angie is in [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

EMP_LIST[DOR Group]​

[/FONT]

3 ​

and October 4th is in Column 3 of [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

I_DOR_ARRAY​

[/FONT]. On the dashboard I want the intersection of Angie and Oct 4th (G9) to say "D"

I've tried many versions of Index (Match) formulas but I can't seem to get it to work and my mind has turned to pure mush.

Any help is appreciated.

[/FONT]

 

[szita2000]

Hey Bruce.

Not sure if this is what you want, but this is so you can have a table with names and a date on one sheet:

	B	C
9	Employee1	01/01/2019
10	Employee2	02/01/2019
11	Employee3

<tbody>
</tbody>

Sheet1

And a matrix style table on Sheet2

	B	C	D	E
6		01/01/2019	02/01/2019	03/01/2019
7
8
9	Employee1	D
10	Employee2		D
11	Employee3

<tbody>
</tbody>

Sheet2

Worksheet Formulas Cell Formula C9 =IF(ISNUMBER(MATCH(INDEX(Sheet1!$C$9:$C$11,MATCH(Sheet2!$B9,Sheet1!$B$9:$B$11,0)),C$6,0)),"D","") <tbody> </tbody>

<tbody>
</tbody>

You can Drag C9 to the right then down.