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
Employee1
Employee2
Employee3

<tbody>
[TD="align: center"]9[/TD]

[TD="align: right"]01/01/2019[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]02/01/2019[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1

And a matrix style table on Sheet2

	B	C	D	E
Employee1	D
Employee2		D
Employee3

<tbody>
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]02/01/2019[/TD]
[TD="align: right"]03/01/2019[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>

Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=IF(ISNUMBER(MATCH(INDEX(Sheet1!$C$9:$C$11,MATCH(Sheet2!$B9,Sheet1!$B$9:$B$11,0)),C$6,0)),"D","")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


You can Drag C9 to the right then down.