INDEX MATCH Multiple Criteria

mohdabrar

New Member
Joined
Aug 4, 2015
Messages
40
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1NMC RUWAIS HOSPITAL DEPT. ROTATIONWeek 1DeptWeekdays MorningsWeekendsWeekdays Afternoons
2MONTUEWEDTHUGeneral MedicineD28:0017:00D410:0019:00A12:0021:009
3NameFTEDepartmentNotesLineNotesCodeStartEndHoursCodeStartEndHoursCodeStartEndHoursCodeStartEndHoursDentalD28:0017:00D511:0020:00A12:0021:009
4Doctor 11General MedicinePediatricsD28:0017:00D511:0020:00A11:0020:009
5Doctor 21General MedicineGynaecologyD28:0017:00D511:0020:00A11:0020:009
6Doctor 31General MedicineDermatologyD39:0018:00D511:0020:00A11:0020:009
7Doctor 41General MedicineOrthopaedicsD28:0017:00D511:0020:00A11:0020:009
8Doctor 51General MedicineInternal MedicineD28:0017:00D511:0020:00A11:0020:009
9Doctor 61General MedicineENTD39:0018:00D511:0020:00 
10Doctor 111Emergency & CasualtyOpthalmologyD39:0018:00D511:0020:00 
11Doctor 121Emergency & CasualtyFamily MedicineD28:0013:00CA17:0020:003
12Doctor 131Emergency & CasualtyGastroenterologyD29:0018:00D511:0020:00
13Doctor 141Emergency & CasualtyUrologyD28:0017:00D511:0020:00
14Doctor 151Emergency & CasualtyPsychiatryD29:0018:00C
15Doctor 161Emergency & CasualtySurgeryD28:0017:00D511:0020:00A11:0020:009
16Doctor 211DentalD28:0017:009D28:0017:009D28:0017:009D28:0017:009CardiologyD28:0017:00D511:0020:00A11:0020:009
17Doctor 221DentalAAAAOccupational HealthD17:0016:00C
18Doctor 231DentalD28:0017:009D28:0017:009D28:0017:009D28:0017:009
19Doctor 241DentalAAAA
20Doctor 251DentalD28:0017:009D28:0017:009D28:0017:009D28:0017:009
21Doctor 261DentalAAAA
22Doctor 291PediatricsD28:0017:009D28:0017:009D28:0017:009D28:0017:009
23Doctor 301PediatricsD28:0017:009D28:0017:009D28:0017:009D28:0017:009
24Doctor 311PediatricsAAAA
25Doctor 321PediatricsAAAA
26Doctor 351GynaecologyD28:0017:009D28:0017:009D28:0017:009D28:0017:009
27Doctor 361GynaecologyD28:0017:009D28:0017:009D28:0017:009D28:0017:009
28Doctor 371GynaecologyAAAA
29Doctor 381GynaecologyAAAA
30Doctor 411DermatologyD39:0018:009D39:0018:009D39:0018:009D39:0018:009
31Doctor 421DermatologyAAAA
32Doctor 451OrthopaedicsD28:0017:009D28:0017:009D28:0017:009D28:0017:009
33Doctor 461OrthopaedicsAAAA
34Doctor 491Internal MedicineD28:0017:009D28:0017:009D28:0017:009D28:0017:009
35Doctor 501Internal MedicineAAAA
36Doctor 521Internal MedicineD28:0017:009D28:0017:009D28:0017:009D28:0017:009
37Doctor 531ENTD39:0018:009D39:0018:009D39:0018:009D39:0018:009
38Doctor 551OpthalmologyD39:0018:009D39:0018:009D39:0018:009D39:0018:009
39Doctor 571Family MedicineD28:0017:009D28:0017:009D28:0017:009D28:0017:009
40Doctor 591GastroenterologyD28:0017:009D28:0017:009D28:0017:009D28:0017:009
41Doctor 611UrologyD28:0017:009D28:0017:009D28:0017:009D28:0017:009
42Doctor 631PsychiatryD28:0017:009D28:0017:009D28:0017:009D28:0017:009
43Doctor 651SurgeryD28:0017:009D28:0017:009D28:0017:009D28:0017:009
44Doctor 661SurgeryAAAA
45Doctor 671CardiologyD28:0017:009D28:0017:009D28:0017:009D28:0017:009
46Doctor 681CardiologyAAAA
47Doctor 691Occupational HealthD17:0016:009D17:0016:009D17:0016:009D17:0016:009
Sheet1
Cell Formulas
RangeFormula
AM2:AM11,AM15:AM16AM2=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
CellConditionCell FormatStop If True
E25Cell Value="LOA"textNO
E24Cell Value="LOA"textNO
E23Cell Value="LOA"textNO
E22Cell Value="LOA"textNO
E22Cell Value="LOA"textNO
E5Cell Value="LOA"textNO
E6:F21,E26:F47Cell Value="LOA"textNO
F22:F23Cell Value="LOA"textNO
F22:F23Cell Value="LOA"textNO
E21:F21Cell Value="LOA"textNO
E4:F4,F24:F25Cell Value="LOA"textNO
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
626
Office Version
  1. 365
Platform
  1. Windows
I'd recommend formatting as tables the times by themselves then the department layout.
scratchbook excel testing.xlsm
CDEFGHIJKLM
2Time CodeStartEndHoursDepartmentTime PeriodCodeStartEndHours
3A111:0020:00 9 General MedicineWeekday MorningsD28:0017:009
4A212:0021:00 9 General MedicineWeekendsD410:0019:009
5A317:0020:00 3 General MedicineWeekday AfternoonsA212:0021:009
6C - DentalWeekday MorningsD28:0017:009
7D17:0016:00 9 DentalWeekendsD511:0020:009
8D28:0017:00 9 DentalWeekday AfternoonsA212:0021:009
9D39:0018:00 9 PediatricsWeekday MorningsD28:0017:009
10D410:0019:00 9 PediatricsWeekendsD511:0020:009
11D511:0020:00 9 PediatricsWeekday AfternoonsA111:0020:009
12GynaecologyWeekday MorningsD28:0017:009
13GynaecologyWeekendsD511:0020:009
14GynaecologyWeekday AfternoonsA111:0020:009
15DermatologyWeekday MorningsD39:0018:009
16DermatologyWeekendsD511:0020:009
17DermatologyWeekday AfternoonsA111:0020:009
18OrthopaedicsWeekday MorningsD28:0017:009
19OrthopaedicsWeekendsD511:0020:009
20OrthopaedicsWeekday AfternoonsA111:0020:009
21Internal MedicineWeekday MorningsD28:0017:009
22Internal MedicineWeekendsD511:0020:009
23Internal MedicineWeekday AfternoonsA111:0020:009
24ENTWeekday MorningsD39:0018:009
25ENTWeekendsD511:0020:009
26OpthalmologyWeekday MorningsD39:0018:009
27OpthalmologyWeekendsD511:0020:009
28Family MedicineWeekday MorningsD28:0017:009
29Family MedicineWeekendsC0:000:000
30Family MedicineWeekday AfternoonsA317:0020:003
31GastroenterologyWeekday MorningsD28:0017:009
32GastroenterologyWeekendsD511:0020:009
33UrologyWeekday MorningsD28:0017:009
34UrologyWeekendsD511:0020:009
35PsychiatryWeekday MorningsD28:0017:009
36PsychiatryWeekendsC0:000:000
37SurgeryWeekday MorningsD28:0017:009
38SurgeryWeekendsD511:0020:009
39SurgeryWeekday AfternoonsA111:0020:009
40CardiologyWeekday MorningsD28:0017:009
41CardiologyWeekendsD511:0020:009
42CardiologyWeekday AfternoonsA111:0020:009
43OccupationWeekday MorningsD17:0016:009
44OccupationWeekendsC0:000:000
Sheet19
Cell Formulas
RangeFormula
K3:K44K3=XLOOKUP([@Code],Table30[Time Code],Table30[Start],,0)
L3:L44L3=XLOOKUP([@Code],Table30[Time Code],Table30[End],,0)
M3:M44M3=XLOOKUP([@Code],Table30[Time Code],Table30[Hours],,0)
F3:F11F3=(E3-D3)*24
 
Solution

Forum statistics

Threads
1,175,999
Messages
5,900,794
Members
434,854
Latest member
ExcelMuffin

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
Top