Argument Based on changing dates

E

emisa

New Member
Joined
Jan 12, 2014
Messages
29
Office Version
  1. 365
Platform
  1. MacOS
Good morning!!!

I have a sheet containing dates B, time C, role D, and Name G. My goal is to have a uniform formula in D, (not sure if the the existing one would work then) from 30/12/24 and so on. The role is identified from the different cell references from K-S and showed in D. However I can only make this on one date 30/12/24. I am stuck and could not move forward. Not sure what argument to use as well. Every day the people roles change which is based from the table K-S.

Thanks for your help and support.

Draft CCC Management Sheet.xlsx
ABCDGHIJKLMNOPQRS
1ShiftsLabelNameStartEnd
2Monday 30/12/2024
3Full Name
412/30/240 Oster, Jessica L07:0014:30
512/30/24C0700-1930 Farrell, Jo A07:0019:30ICUHDU
612/30/24C0700-1530 Admin Day Gilhooly, Andrew J07:0015:30Week StartingCoordinatorAccessCOORDIATORACCESS
712/30/24C0700-1530 CC Lester, Whitney J07:0015:30
812/30/24C0700-1730 Culverwell, Pamela D07:0017:3012/30/24MBrady, KellyFergusson, Karen WBuscke, Deborah EPaul, Noble
912/30/24C0700-1530 Jenkins, Nicole A07:0015:30TBernabe, Jourel CBanayos, Grace EJiang, Phylicia FMathews, Asha
1012/30/24C0700-1930 Tateishi, Seiko07:0019:30
1112/30/24C0700-1930 Kumar, Poonam S07:0019:3012/31/24MPetrie, Elizabeth MNair, Preeja GAnn Roy, RainaMaloco, Mavil V
1212/30/24C0700-1930 Heka, Ninette S07:0019:30T
1312/30/24C0700-1730 Devoy, Kathleen L07:0017:30
1412/30/24C0700-1930 Smith, Chloe J07:0019:30
1512/30/24C0700-1930ICUCOORBrady, Kelly F07:0019:30
1612/30/24C0700-1930HDUACSPaul, Noble07:0019:30
1712/30/24C0700-1930HDUCOORBuscke, Deborah E07:0019:30
1812/30/24C1900-0730HDUCOORJiang, Phylicia F19:0007:30
1912/30/24C1900-0730 Nur, Shabina B19:0007:30
2012/30/24C1900-0730ICUACSBanayos, Grace E19:0007:30
2112/30/24C1900-0730 Crawley, Jonathan L19:0007:30
2212/30/24C1900-0730 Akauola, Kafoatu M19:0007:30
2312/30/24C1900-0730 John, Jainy19:0007:30
2412/30/24C1900-0730HDUACSMathews, Asha19:0007:30
2512/30/24C1900-0730 Misa, Eddieson19:0007:30
2612/30/24C1900-0730 Sahadevan, Savanth19:0007:30
2712/30/24C1900-0730ICUCOORBernabe, Jourel C19:0007:30
28Tuesday 31/12/202412/31/240FALSE
2912/31/240FALSE
3012/31/240FALSEOster, Jessica L07:0014:30
3112/31/24C0700-1930FALSESturrock, Christine M07:0019:30
3212/31/24C0700-1530 CCFALSEDavis, Rachel A07:0015:30
3312/31/24C0700-1530FALSEJenkins, Nicole A07:0015:30
3412/31/24C0700-1730FALSECulverwell, Pamela D07:0017:30
3512/31/24C0700-1930FALSEKafa, Vika F07:0019:30
3612/31/24C0700-1930FALSEUta, Ofisa C07:0019:30
3712/31/24C0700-1930FALSETaviliniu, Evaata07:0019:30
3812/31/24C0700-1930FALSERuiterman, Asuncion07:0019:30
3912/31/24C0700-1730FALSEDevoy, Kathleen L07:0017:30
4012/31/24C0700-1930FALSESmith, Chloe J07:0019:30
4112/31/24C0700-1930FALSEPetrie, Elizabeth M07:0019:30
4212/31/24C0700-1930 OrientatiFALSEWong-Kam, Emma A07:0019:30
4312/31/24C0700-1930FALSEMaloco, Mavil V07:0019:30
4412/31/24C0700-1930FALSEVarghese, Jaisymol07:0019:30
4512/31/24C0700-1930FALSEAnn Roy, Raina07:0019:30
4612/31/24C0700-1930FALSEKochupurackal Saju, Suja07:0019:30
4712/31/24C0700-1930FALSENair, Preeja G07:0019:30
4812/31/24C0700-1930 OrientatiFALSEKumar, Shreta S07:0019:30
Sheet3
Cell Formulas
RangeFormula
C1,C4:C15C1='Raw Data'!H1
B4:B48B4=IF(C4="","",LOOKUP(10^9,A$2:A4))
D4:D48D4=IF(B4=$K$8,IF(ISNUMBER(SEARCH($M$8,G4)),"ICUCOOR",IF(ISNUMBER(SEARCH($O$8,G4)),"ICUACS",IF(ISNUMBER(SEARCH($Q$8,G4)),"HDUCOOR",IF(ISNUMBER(SEARCH($S$8,G4)),"HDUACS",IF(ISNUMBER(SEARCH($M$9,G4)),"ICUCOOR",IF(ISNUMBER(SEARCH($O$9,G4)),"ICUACS",IF(ISNUMBER(SEARCH($Q$9,G4)),"HDUCOOR",IF(ISNUMBER(SEARCH($S$9,G4)),"HDUACS","")))))))))
C16:C17C16='Raw Data'!H26
C18:C27C18='Raw Data'!H69
C28:C48C28='Raw Data'!H84
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Perhaps something like this:

ABCDEFGHIJKLMNOPQR
1ShiftsLabelNameStartEnd
2Mon 30 Dec 2024
3Full Name
430 Dec 2024 Devoy, Kathleen LICUHDU
530 Dec 2024 Smith, Chloe JWeek StartingCoordinatorAccessCOORDIATORACCESS
630 Dec 2024ICUCOORBrady, Kelly FICUCOORICUACSHDUCOORHDUACS
730 Dec 2024HDUACSPaul, Noble30 Dec 2024MBrady, Kelly FFergusson, Karen WBuscke, Deborah EPaul, Noble
830 Dec 2024HDUCOORBuscke, Deborah ETBernabe, Jourel CBanayos, Grace EJiang, Phylicia FMathews, Asha
930 Dec 2024 Jiang, Phylicia F
1030 Dec 2024 Nur, Shabina B31 Dec 2024MPetrie, Elizabeth MNair, Preeja GAnn Roy, RainaMaloco, Mavil V
1130 Dec 2024ICUACSFergusson, Karen WT
1230 Dec 2024 Crawley, Jonathan L
1330 Dec 2024 Akauola, Kafoatu M
1430 Dec 2024 John, Jainy
1530 Dec 2024 Mathews, Asha
1630 Dec 2024 Misa, Eddieson
1730 Dec 2024 Sahadevan, Savanth
1830 Dec 2024 Bernabe, Jourel C
19Tue 31 Dec 202431 Dec 2024
2031 Dec 2024
2131 Dec 2024 Oster, Jessica L
2231 Dec 2024 Sturrock, Christine M
2331 Dec 2024 Smith, Chloe J
2431 Dec 2024ICUCOORPetrie, Elizabeth M
2531 Dec 2024 Wong-Kam, Emma A
2631 Dec 2024HDUACSMaloco, Mavil V
2731 Dec 2024 Varghese, Jaisymol
2831 Dec 2024HDUCOORAnn Roy, Raina
2931 Dec 2024 Kochupurackal Saju, Suja
3031 Dec 2024ICUACSNair, Preeja G
3131 Dec 2024 Kumar, Shreta S
Sheet1
Cell Formulas
RangeFormula
D4:D18,D21:D31D4=XLOOKUP(F4,XLOOKUP(B4,J$7:J$20,L$7:R$20),L$6:R$6,"")
 
Upvote 0
Solution
Hi @StephenCrump,

Thanks for your help. I have put in the formula aand I would say its perfect.

I just have to align some of the name and it works perfectly well

Thanks a lot! Very impressive.
 
Last edited:
Upvote 0
Can you please post your layout again, so we can see how you've implemented the XLOOKUP formula?

EDIT: Please ignore this. It looks you've worked it out and edited your post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Match a Value in a Cell
Replies
5
Views
97
Fluff
Fluff

Forum statistics

Threads
1,225,626
Messages
6,186,092
Members
453,337
Latest member
fiaz ahmad

Share this page

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