Match multiple criteria returns a specific result

bruce24444

New Member
Joined
Feb 15, 2010
Messages
40
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:


BC
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

BCDE
Employee1D
Employee2D
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.
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top