Index and match with multiple criteria

[leonp]

I have three data tables (see below).

• The first one (A1:H12) shows the employee name and whether they are in work on a given day
• The second one (J1:P12) shows what job role they are working on a given day
• The third one (R1:X12) shows what line they are working on

What I need to do is populate the tables below these (A17:E23and A26:E32). I.e. for line one Mondaymanning I need to pull in the names of the people working in each role.

I think it may be some form of index and match formula Iwould need but I can’t figure it out for the life of me. Can anyone help?

Thanks!

LINE ROLE

LINE

Name

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Sun

John A

John B

W

W

W

W

Job 1

Job 1

Job 1

Job 1

1

1

1

1

John C

W

W

W

W

W

Job 1

Job 1

Job 1

Job 1

Job 1

2

2

2

2

2

John D

W

W

W

W

W

Job 2

Job 2

Job 2

Job 2

Job 2

1

5

1

1

1

John E

W

W

W

W

Job 2

Job 2

Job 2

Job 2

1

1

1

1

John F

W

W

W

W

W

Job 3

Job 3

Job 3

Job 3

Job 3

1

1

1

1

1

John G

W

W

W

W

W

Job 3

Job 3

Job 3

Job 3

Job 3

1

1

1

1

1

John H

W

W

W

W

W

Job 3

Job 3

Job 3

Job 3

Job 3

1

1

1

1

1

John I

W

W

W

W

W

Job 4

Job 4

Job 4

Job 4

Job 4

1

1

1

1

1

John J

Line 1 manning - Monday

Headcount

Employees

Job 1

0

Job 2

2

John D

John E

Job 3

2

John F

John H

Job 4

0

Line 2 manning - Tuesday

Headcount

Employees

Job 1

0

Job 2

1

John E

Job 3

3

John F

John G

John H

Job 4

1

John I

<colgroup><col width="156" style="width: 117pt; mso-width-source: userset; mso-width-alt: 5705;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;" span="5"> <col width="64" style="width: 48pt;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;" span="7"> <col width="64" style="width: 48pt;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" span="7"> <tbody>
</tbody>

 

[leonp]

Apologies for the poor formatting, I've tried to use some of the suggested tools for pasting small tables as per the forum rules but am unable to download any of the tools due to restrictions at work

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	Name	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun		Mon	Tue	Wed	Thu	Fri	Sat	Sun
3	John A
4	John B			W	W	W	W					Job 1	Job 1	Job 1	Job 1					1	1	1	1
5	John C		W	W	W	W	W				Job 1	Job 1	Job 1	Job 1	Job 1				2	2	2	2	2
6	John D	W	W	W	W			W		Job 2	Job 2	Job 2	Job 2			Job 2		1	5	1	1			1
7	John E	W	W	W				W		Job 2	Job 2	Job 2				Job 2		1	1	1				1
8	John F	W	W	W	W			W		Job 3	Job 3	Job 3	Job 3			Job 3		1	1	1	1			1
9	John G		W	W	W	W	W				Job 3	Job 3	Job 3	Job 3	Job 3				1	1	1	1	1
10	John H	W	W	W	W			W		Job 3	Job 3	Job 3	Job 3			Job 3		1	1	1	1			1
11	John I		W	W	W	W	W				Job 4	Job 4	Job 4	Job 4	Job 4				1	1	1	1	1
12	John J
13
14
15
16
17	Line 1 manning - Monday	Headcount	Employees
18
19	Job 1	0
20	Job 2	2	John D	John E
21	Job 3	2	John F	John H
22	Job 4	0
End

Cell Formulas
Range	Formula
B19	=COUNTIFS($J$3:$J$12,A19,$R$3:$R$12,1)
C19	=IFERROR(INDEX($A$3:$A$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$3)+1)/(($J$3:$J$12=$A19)*($R$3:$R$12=1)),COLUMNS($A$1:A$1))),"")
D19	=IFERROR(INDEX($A$3:$A$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$3)+1)/(($J$3:$J$12=$A19)*($R$3:$R$12=1)),COLUMNS($A$1:B$1))),"")
E19	=IFERROR(INDEX($A$3:$A$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$3)+1)/(($J$3:$J$12=$A19)*($R$3:$R$12=1)),COLUMNS($A$1:C$1))),"")
F19	=IFERROR(INDEX($A$3:$A$12,AGGREGATE(15,6,(ROW($A$3:$A$12)-ROW($A$3)+1)/(($J$3:$J$12=$A19)*($R$3:$R$12=1)),COLUMNS($A$1:D$1))),"")