Index Match Formula to generate a report duplicates? Rosters/Vacancies

[Jay88]

G'day from Australia!

Long time lurker, first time poster!


Getting a bit stumped figuring a formula to generate a report using INDEX MATCH to detail all "VACANT" positions.

This roster is an example (which generally varies in rows and titles) the only "fixed points" I can constantly refer to are;

• Area=A:A
• Section=B:B
• Shift Timings=C:C
• Role=E:E
• Shift Length (2 cells underneath VACANT/Employee Name)
• Alongside the day columns (i.e Mon= F:F, Tue=G:G, Wed=H:H) for Weeks 1 & 2
  
  

Please see the example below for an illustration (populated rosters generally expand over 300 rows)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2	AM	Clinical Management												AM	Clinical Management
3	Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Clinical Mngmt		09:00 - 17:06		CM	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name				Clinical Mngmt		09:00 - 17:06		CM	VACANT	VACANT	Employee Name	Employee Name	Employee Name
5
6			7.6	38		7.6	7.6	7.6	7.6	7.6						7.6	38		7.6	7.6	7.6	7.6	7.6
7	Clinical Mngmt		07:00 - 15:00		RN						Employee Name	Employee Name		Clinical Mngmt		07:00 - 15:00		RN						Employee Name	Employee Name
8
9			7.5	15							7.5	7.5				7.5	15							7.5	7.5
10	Clinical Mngmt		07:00 - 15:00		RN	Employee Name	VACANT	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		Clinical Mngmt		07:00 - 15:00		RN	Employee Name	VACANT	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
11
12			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
13	Clinical Mngmt		14:45 - 22:15		RN	Employee Name	Employee Name	Employee Name	VACANT	VACANT	VACANT	Employee Name		Clinical Mngmt		14:45 - 22:15		RN	Employee Name	Employee Name	Employee Name	VACANT	VACANT	VACANT	Employee Name
14
15			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
16	Clinical Mngmt		22:00 - 07:15		RN	Employee Name	Employee Name	VACANT	VACANT	Employee Name	Employee Name	Employee Name		Clinical Mngmt		22:00 - 07:15		RN	Employee Name	Employee Name	VACANT	VACANT	Employee Name	Employee Name	Employee Name
17
18			9.25	64.75		9.25	9.25	9.25	9.25	9.25	9.25	9.25				9.25	64.75		9.25	9.25	9.25	9.25	9.25	9.25	9.25
19
20	AM	North A & B (65 Beds)												AM	North A & B (65 Beds)
21	Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun
22	North Wing		07:00 - 15:00		RN	VACANT	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	VACANT		North Wing		07:00 - 15:00		RN	VACANT	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	VACANT
23
24			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
25	North Wing		07:00 - 14:30		EEN	Employee Name	Employee Name	Employee Name	Employee Name	VACANT	VACANT	Employee Name		North Wing		07:00 - 14:30		EEN	Employee Name	Employee Name	Employee Name	Employee Name	VACANT	VACANT	Employee Name
26
27			7	49		7	7	7	7	7	7	7				7	49		7	7	7	7	7	7	7
28
29	PM	North A & B (65 Beds)												PM	North A & B (65 Beds)
30	Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun
31	North Wing		14:45 - 22:00		RN	Employee Name	Employee Name	VACANT	VACANT	VACANT	Employee Name	Employee Name		North Wing		14:45 - 22:00		RN	Employee Name	Employee Name	VACANT	VACANT	VACANT	Employee Name	Employee Name
32
33			6.75	47.25		6.75	6.75	6.75	6.75	6.75	6.75	6.75				6.75	47.25		6.75	6.75	6.75	6.75	6.75	6.75	6.75
34	North Wing		15:00 - 21:00		RN	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing		15:00 - 21:00		RN	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
35
36			5.5	38.5		5.5	5.5	5.5	5.5	5.5	5.5	5.5				5.5	38.5		5.5	5.5	5.5	5.5	5.5	5.5	5.5
37
38	AM	North A & B (65 Beds)												AM	North A & B (65 Beds)
39	Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun		Area	Section	Shift	WK Hrs	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun
40	North Wing	A	07:00 - 14:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing	A	07:00 - 14:00		PCA	VACANT	VACANT	VACANT	VACANT	VACANT	VACANT	VACANT
41
42			6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5				6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5
43	North Wing	A	07:00 - 14:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing	A	07:00 - 14:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
44
45			6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5				6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5
46	North Wing	B	07:00 - 14:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing	B	07:00 - 14:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
47
48			6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5				6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5
49	North Wing	B	07:00 - 14:00		PCA	Employee Name	Employee Name	VACANT	VACANT	Employee Name	Employee Name	Employee Name		North Wing	B	07:00 - 14:00		PCA	Employee Name	Employee Name	VACANT	VACANT	Employee Name	Employee Name	Employee Name
50
51			6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5				6.5	45.5		6.5	6.5	6.5	6.5	6.5	6.5	6.5
52	North Wing	A	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing	A	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
53
54			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
55	North Wing	A	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	VACANT	VACANT		North Wing	A	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	VACANT	VACANT
56
57			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
58	North Wing	B	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name		North Wing	B	07:00 - 15:00		PCA	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name	Employee Name
59
60			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
61	North Wing	B	07:00 - 15:00		PCA	Employee Name	Employee Name	VACANT	Employee Name	VACANT	Employee Name	Employee Name		North Wing	B	07:00 - 15:00		PCA	Employee Name	Employee Name	VACANT	Employee Name	VACANT	Employee Name	Employee Name
62
63			7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5				7.5	52.5		7.5	7.5	7.5	7.5	7.5	7.5	7.5
64		Total AM Hours		392											Total AM Hours		392
Roster

Cell Formulas
Range	Formula
D6,Q6	D6	=SUM(F6:J6)
F6:J6,S60:Y60,F60:L60,S57:Y57,F57:L57,S54:Y54,F54:L54,S51:Y51,F51:L51,S48:Y48,F48:L48,S45:Y45,F45:L45,S42:Y42,F42:L42,S36:Y36,F36:L36,S33:Y33,F33:L33,S27:Y27,F27:L27,S24:Y24,F24:L24,S18:Y18,F18:L18,S15:Y15,F15:L15,S12:Y12,F12:L12,S6:W6	F6	=$C6
D9,Q9	D9	=SUM(K9:L9)
K9:L9,X9:Y9	K9	=$C$9
D12,Q63,D63,Q60,D60,Q57,D57,Q54,D54,Q51,D51,Q48,D48,Q45,D45,Q42,D42,Q36,D36,Q33,D33,Q27,D27,Q24,D24,Q18,D18,Q15,D15,Q12	D12	=SUM(F12:L12)
F63:L63,S63:Y63	F63	=$C$63
D64,Q64	D64	=SUM(D42:D63)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S:Y	Cell Value	="VACANT"	text	NO
F1:L132,F134:L147,I133:L133,F149:L1048576,G148:L148	Cell Value	="VACANT"	text	NO

Ideally, I am seeking a way to generate an easy-to-read report of vacancies, perhaps, something like below? PS. by all means this particular format is not obligatory, just a quick mockup, should you have a more clearer or elegant solution, I would be grateful to hear your thoughts.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Vacancy Report
2					Week 1							Week 2
3	Area	Section	Hours	Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Clinical Mngmt		15.2	CM								09:00 - 17:06	09:00 - 17:07
5	Clinical Mngmt		15	RN		07:00 - 15:00							07:00 - 15:00
6	Clinical Mngmt		45	RN				14:45 - 22:15	14:45 - 22:15	14:45 - 22:15					14:45 - 22:15	14:45 - 22:15	14:45 - 22:15
7	North Wing	A
8	North Wing	A
9	North Wing	B
10	North Wing	B
11	North Wing	A
12	North Wing	A
13	South Wing	B
14	South Wing	B
15	South Wing	B
16	South Wing	A
17	South Wing	A
18	South Wing	A
19	Therapy
20	Therapy
21
Report

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:R20	Cell	contains a blank value	text	NO
L3:R3	Cell Value	="VACANT"	text	NO
E3:K3	Cell Value	="VACANT"	text	NO

Thanks in advance,

Jason

 

[awoohaw]

Hi happy to see your first post!
I have a few observations and questions though.
It is really really tough to flush out one kind of report from another. Your first report has merged cells and is much like a complex cross tab report.
Do you have a list of data that can make everyone's task regarding your reporting much easier, something with columns like this:
Area=A:A
Section=B:B
Shift Timings=C:C
Role=E:E
Shift Start Date:
Shift Start Time:
Shift End Date:
Shift End time:
(note: the start and end dates/times can also be combined into a Date/Time Value)
Vacant/Present

if you have data like this you can easily make any kind of report on staffing.

 

[ExceLoki]

try this and let me know if this work for what you're looking for
-------------------------

Rosters-Vacancies.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Vacancy Report
2						Week 1							Week 2
3	Area	Section	Hours		Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Clinical Mngmt		09:00 - 17:06		CM								09:00 - 17:06	09:00 - 17:06
5	Clinical Mngmt		07:00 - 15:00		RN		07:00 - 15:00							07:00 - 15:00
6	Clinical Mngmt		07:00 - 15:00		RN		07:00 - 15:00							07:00 - 15:00
7	Clinical Mngmt		14:45 - 22:15		RN				14:45 - 22:15	14:45 - 22:15	14:45 - 22:15					14:45 - 22:15	14:45 - 22:15	14:45 - 22:15
8	Clinical Mngmt		22:00 - 07:15		RN			22:00 - 07:15	22:00 - 07:15						22:00 - 07:15	22:00 - 07:15
9	North Wing		07:00 - 15:00		RN	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00
10	North Wing		07:00 - 14:30		EEN					07:00 - 14:30	07:00 - 14:30						07:00 - 14:30	07:00 - 14:30
11	North Wing		14:45 - 22:00		RN			14:45 - 22:00	14:45 - 22:00	14:45 - 22:00					14:45 - 22:00	14:45 - 22:00	14:45 - 22:00
12	North Wing		15:00 - 21:00		RN
13	North Wing	A	07:00 - 14:00		PCA								07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00
14	North Wing	A	07:00 - 14:00		PCA								07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00
15	North Wing	B	07:00 - 14:00		PCA			07:00 - 14:00	07:00 - 14:00						07:00 - 14:00	07:00 - 14:00
16	North Wing	B	07:00 - 14:00		PCA			07:00 - 14:00	07:00 - 14:00						07:00 - 14:00	07:00 - 14:00
17	North Wing	A	07:00 - 15:00		PCA						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00
18	North Wing	A	07:00 - 15:00		PCA						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00
19	North Wing	B	07:00 - 15:00		PCA			07:00 - 15:00		07:00 - 15:00					07:00 - 15:00		07:00 - 15:00
20	North Wing	B	07:00 - 15:00		PCA			07:00 - 15:00		07:00 - 15:00					07:00 - 15:00		07:00 - 15:00
2019 report

Cell Formulas
Range	Formula
F4:L20	F4	=IFERROR(FILTER(Roster!$C$4:$C$63,("VACANT"=Roster!F$4:F$63)*($E4=Roster!$E$4:$E$63)*($A4=Roster!$A$4:$A$63)*($C4=Roster!$C$4:$C$63)*($B4=Roster!$B$4:$B$63)),"")
M4:S20	M4	=IFERROR(FILTER(Roster!$P$4:$P$63,("VACANT"=Roster!S$4:S$63)*($E4=Roster!$R$4:$R$63)*($A4=Roster!$N$4:$N$63)*($C4=Roster!$P$4:$P$63)*($B4=Roster!$O$4:$O$63)),"")

 

[Jay88]

try this and let me know if this work for what you're looking for
-------------------------

Rosters-Vacancies.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Vacancy Report
2						Week 1							Week 2
3	Area	Section	Hours		Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Clinical Mngmt		09:00 - 17:06		CM								09:00 - 17:06	09:00 - 17:06
5	Clinical Mngmt		07:00 - 15:00		RN		07:00 - 15:00							07:00 - 15:00
6	Clinical Mngmt		07:00 - 15:00		RN		07:00 - 15:00							07:00 - 15:00
7	Clinical Mngmt		14:45 - 22:15		RN				14:45 - 22:15	14:45 - 22:15	14:45 - 22:15					14:45 - 22:15	14:45 - 22:15	14:45 - 22:15
8	Clinical Mngmt		22:00 - 07:15		RN			22:00 - 07:15	22:00 - 07:15						22:00 - 07:15	22:00 - 07:15
9	North Wing		07:00 - 15:00		RN	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00
10	North Wing		07:00 - 14:30		EEN					07:00 - 14:30	07:00 - 14:30						07:00 - 14:30	07:00 - 14:30
11	North Wing		14:45 - 22:00		RN			14:45 - 22:00	14:45 - 22:00	14:45 - 22:00					14:45 - 22:00	14:45 - 22:00	14:45 - 22:00
12	North Wing		15:00 - 21:00		RN
13	North Wing	A	07:00 - 14:00		PCA								07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00
14	North Wing	A	07:00 - 14:00		PCA								07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00
15	North Wing	B	07:00 - 14:00		PCA			07:00 - 14:00	07:00 - 14:00						07:00 - 14:00	07:00 - 14:00
16	North Wing	B	07:00 - 14:00		PCA			07:00 - 14:00	07:00 - 14:00						07:00 - 14:00	07:00 - 14:00
17	North Wing	A	07:00 - 15:00		PCA						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00
18	North Wing	A	07:00 - 15:00		PCA						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00
19	North Wing	B	07:00 - 15:00		PCA			07:00 - 15:00		07:00 - 15:00					07:00 - 15:00		07:00 - 15:00
20	North Wing	B	07:00 - 15:00		PCA			07:00 - 15:00		07:00 - 15:00					07:00 - 15:00		07:00 - 15:00
2019 report

Cell Formulas
Range	Formula
F4:L20	F4	=IFERROR(FILTER(Roster!$C$4:$C$63,("VACANT"=Roster!F$4:F$63)*($E4=Roster!$E$4:$E$63)*($A4=Roster!$A$4:$A$63)*($C4=Roster!$C$4:$C$63)*($B4=Roster!$B$4:$B$63)),"")
M4:S20	M4	=IFERROR(FILTER(Roster!$P$4:$P$63,("VACANT"=Roster!S$4:S$63)*($E4=Roster!$R$4:$R$63)*($A4=Roster!$N$4:$N$63)*($C4=Roster!$P$4:$P$63)*($B4=Roster!$O$4:$O$63)),"")

You’re solution would definitely work, thank you for your help! Assuming that you could carry the cells across to return the other cells too? eg Area, Section, Role..

Unfortunately, I’m restricted to Excel 2019 and cannot use the FILTER function.

Do you know of any alternative functions that could work above?

Thanks again,
Jason

 

[Jay88]

Hi happy to see your first post!
I have a few observations and questions though.
It is really really tough to flush out one kind of report from another. Your first report has merged cells and is much like a complex cross tab report.
Do you have a list of data that can make everyone's task regarding your reporting much easier, something with columns like this:
Area=A:A
Section=B:B
Shift Timings=C:C
Role=E:E
Shift Start Date:
Shift Start Time:
Shift End Date:
Shift End time:
(note: the start and end dates/times can also be combined into a Date/Time Value)
Vacant/Present

if you have data like this you can easily make any kind of report on staffing.

Thanks mate! Totally agree with you, unfortunately I don’t have a particular source where the first report is derived from, they are created from scratch for each facility, hence things like the merged cells (however that is purely for aesthetics and can be removed if necessary).

For context, I work in Human Resources, where I would rebuild master rosters, consult employees on new shifts and once everyone is happy with the new roster, I would send this roster to another department which migrates the data to an online scheduling system (lots of manual labour involved hey!).

 

[ExceLoki]

You’re solution would definitely work, thank you for your help! Assuming that you could carry the cells across to return the other cells too? eg Area, Section, Role..

Unfortunately, I’m restricted to Excel 2019 and cannot use the FILTER function.

Do you know of any alternative functions that could work above?

Thanks again,
Jason

according to the microsoft site here: FILTER function - Microsoft Support, you should be able to use FILTER on excel 2019. that's why i created the formula the way i did. if you're not able to use it, can look for another way.

 

[Fluff]

That site is unreliable & in this case wrong. The Filter function is only available in 365 & 2021.
A more reliable site is Excel functions (alphabetical) - Microsoft Support or Excel functions (by category) - Microsoft Support

 

[ExceLoki]

try this

Rosters-Vacancies.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Vacancy Report
2						Week 1							Week 2
3	Area	Section	Hours		Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4	Clinical Mngmt		09:00 - 17:06		CM								09:00 - 17:06	09:00 - 17:06
5	Clinical Mngmt		07:00 - 15:00		RN
6	Clinical Mngmt		14:45 - 22:15		RN				14:45 - 22:15	14:45 - 22:15	14:45 - 22:15					14:45 - 22:15	14:45 - 22:15	14:45 - 22:15
7	Clinical Mngmt		22:00 - 07:15		RN			22:00 - 07:15	22:00 - 07:15						22:00 - 07:15	22:00 - 07:15
8	North Wing		07:00 - 15:00		RN	07:00 - 15:00						07:00 - 15:00	07:00 - 15:00						07:00 - 15:00
9	North Wing		07:00 - 14:30		EEN					07:00 - 14:30	07:00 - 14:30						07:00 - 14:30	07:00 - 14:30
10	North Wing		14:45 - 22:00		RN			14:45 - 22:00	14:45 - 22:00	14:45 - 22:00					14:45 - 22:00	14:45 - 22:00	14:45 - 22:00
11	North Wing		15:00 - 21:00		RN
12	North Wing	A	07:00 - 14:00		PCA								07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00	07:00 - 14:00
13	North Wing	B	07:00 - 14:00		PCA
14	North Wing	A	07:00 - 15:00		PCA
15	North Wing	B	07:00 - 15:00		PCA
2019 report

Cell Formulas
Range	Formula
F4:L15	F4	=IF(INDEX(Roster!$F$4:$L$63,MATCH($E4&$C4&$A4&$B4,Roster!$E$4:$E$63&Roster!$C$4:$C$63&Roster!$A$4:$A$63&Roster!$B$4:$B$63,0),MATCH(F$3,Roster!$F$3:$L$3,0))="VACANT",$C4,"")
M4:S15	M4	=IF(INDEX(Roster!$S$4:$Y$63,MATCH($E4&$C4&$A4&$B4,Roster!$R$4:$R$63&Roster!$P$4:$P$63&Roster!$N$4:$N$63&Roster!$O$4:$O$63,0),MATCH(M$3,Roster!$S$3:$Y$3,0))="VACANT",$C4,"")

 

[Jay88]

try this

Thanks Loki, your formula certainly worked!!! It just requires an update to make it portable and show Area, Section, Hours, Role?


Essentially, find "VACANT" return the corresponding: Area, Section, Hours, Role


Removing the hardcoded text in A:A to E:E produces the result below.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Vacancy Report
2						Week 1							Week 2
3	Area	Section	Hours		Role	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
4
5
6
7
8
9
10
11
12
13
14
15
Sheet6

Cell Formulas
Range	Formula
F4:L15	F4	=IF(INDEX(Roster!$F$4:$L$63,MATCH($E4&$C4&$A4&$B4,Roster!$E$4:$E$63&Roster!$C$4:$C$63&Roster!$A$4:$A$63&Roster!$B$4:$B$63,0),MATCH(F$3,Roster!$F$3:$L$3,0))="VACANT",$C4,"")
M4:S15	M4	=IF(INDEX(Roster!$S$4:$Y$63,MATCH($E4&$C4&$A4&$B4,Roster!$R$4:$R$63&Roster!$P$4:$P$63&Roster!$N$4:$N$63&Roster!$O$4:$O$63,0),MATCH(M$3,Roster!$S$3:$Y$3,0))="VACANT",$C4,"")

 

[Jay88]

That site is unreliable & in this case wrong. The Filter function is only available in 365 & 2021.
A more reliable site is Excel functions (alphabetical) - Microsoft Support or Excel functions (by category) - Microsoft Support

Thats a really good resource, thanks Fluff!