INDEX MATCH Multiple Criteria

[mohdabrar]

Hi,

I have the below data, a schedule that I am trying to make. I have departments in Column "C", and the department work times from Colum AA to column AM, A simple V lookup can get me the times for all, but the departments have different operational times and I need help to look up the time as per the department. Thanks in advance for any help.

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	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	NMC RUWAIS HOSPITAL DEPT. ROTATION						Week 1																				Dept	Weekdays Mornings				Weekends				Weekdays Afternoons
2							MON				TUE				WED				THU								General Medicine	D2	8:00	17:00		D4	10:00	19:00		A	12:00	21:00	9
3	Name	FTE	Department	Notes	Line	Notes	Code	Start	End	Hours	Code	Start	End	Hours	Code	Start	End	Hours	Code	Start	End	Hours					Dental	D2	8:00	17:00		D5	11:00	20:00		A	12:00	21:00	9
4	Doctor 1	1	General Medicine																								Pediatrics	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
5	Doctor 2	1	General Medicine																								Gynaecology	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
6	Doctor 3	1	General Medicine																								Dermatology	D3	9:00	18:00		D5	11:00	20:00		A	11:00	20:00	9
7	Doctor 4	1	General Medicine																								Orthopaedics	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
8	Doctor 5	1	General Medicine																								Internal Medicine	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
9	Doctor 6	1	General Medicine																								ENT	D3	9:00	18:00		D5	11:00	20:00
10	Doctor 11	1	Emergency & Casualty																								Opthalmology	D3	9:00	18:00		D5	11:00	20:00
11	Doctor 12	1	Emergency & Casualty																								Family Medicine	D2	8:00	13:00		C				A	17:00	20:00	3
12	Doctor 13	1	Emergency & Casualty																								Gastroenterology	D2	9:00	18:00		D5	11:00	20:00
13	Doctor 14	1	Emergency & Casualty																								Urology	D2	8:00	17:00		D5	11:00	20:00
14	Doctor 15	1	Emergency & Casualty																								Psychiatry	D2	9:00	18:00		C
15	Doctor 16	1	Emergency & Casualty																								Surgery	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
16	Doctor 21	1	Dental				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9					Cardiology	D2	8:00	17:00		D5	11:00	20:00		A	11:00	20:00	9
17	Doctor 22	1	Dental				A				A				A				A								Occupational Health	D1	7:00	16:00		C
18	Doctor 23	1	Dental				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
19	Doctor 24	1	Dental				A				A				A				A
20	Doctor 25	1	Dental				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
21	Doctor 26	1	Dental				A				A				A				A
22	Doctor 29	1	Pediatrics				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
23	Doctor 30	1	Pediatrics				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
24	Doctor 31	1	Pediatrics				A				A				A				A
25	Doctor 32	1	Pediatrics				A				A				A				A
26	Doctor 35	1	Gynaecology				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
27	Doctor 36	1	Gynaecology				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
28	Doctor 37	1	Gynaecology				A				A				A				A
29	Doctor 38	1	Gynaecology				A				A				A				A
30	Doctor 41	1	Dermatology				D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9
31	Doctor 42	1	Dermatology				A				A				A				A
32	Doctor 45	1	Orthopaedics				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
33	Doctor 46	1	Orthopaedics				A				A				A				A
34	Doctor 49	1	Internal Medicine				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
35	Doctor 50	1	Internal Medicine				A				A				A				A
36	Doctor 52	1	Internal Medicine				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
37	Doctor 53	1	ENT				D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9
38	Doctor 55	1	Opthalmology				D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9	D3	9:00	18:00	9
39	Doctor 57	1	Family Medicine				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
40	Doctor 59	1	Gastroenterology				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
41	Doctor 61	1	Urology				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
42	Doctor 63	1	Psychiatry				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
43	Doctor 65	1	Surgery				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
44	Doctor 66	1	Surgery				A				A				A				A
45	Doctor 67	1	Cardiology				D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9	D2	8:00	17:00	9
46	Doctor 68	1	Cardiology				A				A				A				A
47	Doctor 69	1	Occupational Health				D1	7:00	16:00	9	D1	7:00	16:00	9	D1	7:00	16:00	9	D1	7:00	16:00	9
Sheet1

Cell Formulas
Range	Formula
AM2:AM11,AM15:AM16	AM2	=IF((AK2=""),"",(AL2-AK2+(AL2<AK2))*24-IF((AL2-AK2+(AL2<AK2))*24,IF((AL2-AK2+(AL2<AK2))*24,0,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E25	Cell Value	="LOA"	text	NO
E24	Cell Value	="LOA"	text	NO
E23	Cell Value	="LOA"	text	NO
E22	Cell Value	="LOA"	text	NO
E22	Cell Value	="LOA"	text	NO
E5	Cell Value	="LOA"	text	NO
E6:F21,E26:F47	Cell Value	="LOA"	text	NO
F22:F23	Cell Value	="LOA"	text	NO
F22:F23	Cell Value	="LOA"	text	NO
E21:F21	Cell Value	="LOA"	text	NO
E4:F4,F24:F25	Cell Value	="LOA"	text	NO

 

[C Moore]

I'd recommend formatting as tables the times by themselves then the department layout.

scratchbook excel testing.xlsm
	C	D	E	F	G	H	I	J	K	L	M
2	Time Code	Start	End	Hours		Department	Time Period	Code	Start	End	Hours
3	A1	11:00	20:00	9		General Medicine	Weekday Mornings	D2	8:00	17:00	9
4	A2	12:00	21:00	9		General Medicine	Weekends	D4	10:00	19:00	9
5	A3	17:00	20:00	3		General Medicine	Weekday Afternoons	A2	12:00	21:00	9
6	C			-		Dental	Weekday Mornings	D2	8:00	17:00	9
7	D1	7:00	16:00	9		Dental	Weekends	D5	11:00	20:00	9
8	D2	8:00	17:00	9		Dental	Weekday Afternoons	A2	12:00	21:00	9
9	D3	9:00	18:00	9		Pediatrics	Weekday Mornings	D2	8:00	17:00	9
10	D4	10:00	19:00	9		Pediatrics	Weekends	D5	11:00	20:00	9
11	D5	11:00	20:00	9		Pediatrics	Weekday Afternoons	A1	11:00	20:00	9
12						Gynaecology	Weekday Mornings	D2	8:00	17:00	9
13						Gynaecology	Weekends	D5	11:00	20:00	9
14						Gynaecology	Weekday Afternoons	A1	11:00	20:00	9
15						Dermatology	Weekday Mornings	D3	9:00	18:00	9
16						Dermatology	Weekends	D5	11:00	20:00	9
17						Dermatology	Weekday Afternoons	A1	11:00	20:00	9
18						Orthopaedics	Weekday Mornings	D2	8:00	17:00	9
19						Orthopaedics	Weekends	D5	11:00	20:00	9
20						Orthopaedics	Weekday Afternoons	A1	11:00	20:00	9
21						Internal Medicine	Weekday Mornings	D2	8:00	17:00	9
22						Internal Medicine	Weekends	D5	11:00	20:00	9
23						Internal Medicine	Weekday Afternoons	A1	11:00	20:00	9
24						ENT	Weekday Mornings	D3	9:00	18:00	9
25						ENT	Weekends	D5	11:00	20:00	9
26						Opthalmology	Weekday Mornings	D3	9:00	18:00	9
27						Opthalmology	Weekends	D5	11:00	20:00	9
28						Family Medicine	Weekday Mornings	D2	8:00	17:00	9
29						Family Medicine	Weekends	C	0:00	0:00	0
30						Family Medicine	Weekday Afternoons	A3	17:00	20:00	3
31						Gastroenterology	Weekday Mornings	D2	8:00	17:00	9
32						Gastroenterology	Weekends	D5	11:00	20:00	9
33						Urology	Weekday Mornings	D2	8:00	17:00	9
34						Urology	Weekends	D5	11:00	20:00	9
35						Psychiatry	Weekday Mornings	D2	8:00	17:00	9
36						Psychiatry	Weekends	C	0:00	0:00	0
37						Surgery	Weekday Mornings	D2	8:00	17:00	9
38						Surgery	Weekends	D5	11:00	20:00	9
39						Surgery	Weekday Afternoons	A1	11:00	20:00	9
40						Cardiology	Weekday Mornings	D2	8:00	17:00	9
41						Cardiology	Weekends	D5	11:00	20:00	9
42						Cardiology	Weekday Afternoons	A1	11:00	20:00	9
43						Occupation	Weekday Mornings	D1	7:00	16:00	9
44						Occupation	Weekends	C	0:00	0:00	0
Sheet19

Cell Formulas
Range	Formula
K3:K44	K3	=XLOOKUP([@Code],Table30[Time Code],Table30[Start],,0)
L3:L44	L3	=XLOOKUP([@Code],Table30[Time Code],Table30[End],,0)
M3:M44	M3	=XLOOKUP([@Code],Table30[Time Code],Table30[Hours],,0)
F3:F11	F3	=(E3-D3)*24

 

[mohdabrar]

Thank you! That helped!