XLOOKUP help (maybe), not sure what function to use

M

MeaclH

Board Regular
Joined
Apr 2, 2014
Messages
94
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
G'day.
Am in need of some help and I'm not totally sure where to start in getting this to work. Maybe an XLOOKUP, but I really can't figure out how to lookup one value, and then another, and then produce the result.

I have attached part of the sheet via XL2BB below.
What I need is the table on the right with the data valiadtion date drop down, which is the 14 days of the roster, to populate with the name of the employees rostered to work each shift. The table is currently in the end state, but not sure how to get it there. The cell should first lookup the date in V7, then find that day in the main roster, then look up the shift below, and then finally return the result of the employee in column C.

Hope this makes sense

DIGITAL ROSTER PROJECT 2024 (latest).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1FORTNIGHT COMMENCINGPUBLISHEDSUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
22-Jun3-Jun4-Jun5-Jun6-Jun7-Jun8-Jun9-Jun10-Jun11-Jun12-Jun13-Jun14-Jun15-Jun
302-June-2024DAO NameOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen ShiftsOpen Shifts
4#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!
5
6Sunday, 2 June 2024
7
8SHIFT EMPLOYEE
9Line 1Rostered DAOFEEGRADO. R9ShiftOFF1330BC1330PB1330CFD1330PL/SEDOOFFOFF1330BC1330PB1330CFD1330PL/S1330NRNOFFFIXED VALUES!DYNAMIC VALUES THAT NEED FINDING
10Extension Pre0530PBREEVES. S
11Covered by DAORostered Times 13:3013:3013:3013:30 13:3013:3013:3013:3013:30 0530PL/SENOKA. H
12Remarks 21:4521:4521:4521:45 21:4521:4521:4521:4521:45 0530CFDN/R
13LeaveExtension Post0530NRNN/R
14Line 2Rostered DAORAUF. F10Shift0530NCC0530PL/SOFFOFF0530NRN0530BC0530PB0530NCC0530PL/SOFFOFF0530CFD0530PL/S0530BC0530NCCRAUF. F
15Extension Pre0530BCWILSON. M
16Covered by DAORostered Times5:305:30 5:305:305:305:305:30 5:305:305:30
17Remarks13:4513:45 13:4513:4513:4513:4513:45 13:4513:4513:451330PBBOVELL. S
18LeaveExtension Post1330PL/STHEODOROPOULOS. A
19Line 3Rostered DAOSALEH. M9Shift2130PL/S2130BCOFFOFFOFF2130PB2130NCC2130PL/S2130BC2130PBEDOOFF2130PL/S2130BC1330CFDN/R
20Extension Pre1330NRNN/R
21Covered by DAORostered Times21:3021:30 21:3021:3021:3021:3021:30 21:3021:301330NCCMILINOVIC. S
22Remarks5:455:45 5:455:455:455:455:45 5:455:451330BCMUNAGALA. V
23LeaveExtension Post
24Line 4Rostered DAORUBERTO. R10ShiftOFFOFF1330CFD1330PL/S1330NRN1330BC1330PB1330NCC1330PL/SOFFOFF1330NRN1330BC1330PB2130PBSAMI. T
25Extension Pre2130PL/SSALEH. M
26Covered by DAORostered Times 13:3013:3013:3013:3013:3013:3013:30 13:3013:3013:302130NCCDIBBEN L
27Remarks 21:4521:4521:4521:4521:4521:4521:45 21:4521:4521:452130BCSALEH. K
28LeaveExtension Post
29Line 5Rostered DAOMEACLEM. H9ShiftOFFOFF2130PL/S2130BC2130PB2130NCC2130PL/SOFF2130PL/S2130BC2130PB2130NCCEDOOFF
30Extension Pre
31Covered by DAORostered Times 21:3021:3021:3021:3021:30 21:3021:3021:3021:30
32Remarks 5:455:455:455:455:45 5:455:455:455:45
33LeaveExtension Post
34Line 6Rostered DAOENOKA. H10Shift0530PL/S0530NRN0530DVOFFOFF0530NRN0530BC0530PB0530CFD0530PL/S0530NRN0530PBOFFOFF
35Extension Pre
36Covered by DAORostered Times5:305:30#N/A 5:305:305:305:305:305:305:30
37Remarks13:4513:45#N/A 13:4513:4513:4513:4513:4513:4513:45
38LeaveExtension Post
39Line 7Rostered DAOMILINOVIC. S9Shift1330NCC1330PL/S1330NRN1330BCEDOOFFOFFOFFOFF1330NRN1330BC1330PB1330CFD1330PL/S
40Extension Pre
41Covered by DAORostered Times13:3013:3013:3013:30 13:3013:3013:3013:3013:30
42Remarks21:4521:4521:4521:45 21:4521:4521:4521:4521:45
43LeaveExtension Post
44Line 8Rostered DAOSAMI. T10Shift2130PB2130NCCOFFOFF2130NCC2130PL/S2130BC2130PB2130NCCOFFOFF2130BC2130PB2130NCC
45Extension Pre
46Covered by DAORostered Times21:3021:30 21:3021:3021:3021:3021:30 21:3021:3021:30
47Remarks5:455:45 5:455:455:455:455:45 5:455:455:45
48LeaveExtension Post
49Line 9Rostered DAOSVARC. S9ShiftOFFEDO0530NRN0530BC0530PB0530CFDOFFOFF0530NRN0530BC0530PBOFF0530CFD0530PL/S
50Extension Pre
51Covered by DAORostered Times 5:305:305:305:30 5:305:305:30 5:305:30
52Remarks 13:4513:4513:4513:45 13:4513:4513:45 13:4513:45
53LeaveExtension Post
54Line 10Rostered DAOTHEODOROPOLOUS. A10Shift1330PL/S1330NRN1330BCOFFOFF1330NRN1330BC1330PB1330CFD1330PL/S1330NRNOFFOFF1330NCC
55Extension Pre
56Covered by DAORostered Times13:3013:3013:30 13:3013:3013:3013:3013:3013:30 13:30
57Remarks21:4521:4521:45 21:4521:4521:4521:4521:4521:45 21:45
58LeaveExtension Post
Sheet1
Cell Formulas
RangeFormula
G2G2=IF(A3="","",A3)
H2:T2H2=IF(G2="","",G2+1)
G4,M4:N4,T4G4=FILTER(DATA!$AF$2:$AF$13,NOT(COUNTIF(G9:G108,DATA!$AF$2:$AF$13)))
H4:L4,O4:S4H4=FILTER(DATA!$AG$2:$AG$15,NOT(COUNTIF(H9:H108,DATA!$AG$2:$AG$15)))
C9,C54,C49,C44,C39,C34,C29,C24,C19,C14C9=IF($A$3="","",VLOOKUP(CONCATENATE(VALUE($A$3),$A9),DATA!$C$2:$D$1567,2,0))
G11:T11,G56:T56,G51:T51,G46:T46,G41:T41,G36:T36,G31:T31,G26:T26,G21:T21,G16:T16G11=VLOOKUP(G9,DATA!$Z$3:$AB$38,2,FALSE)
G12:T12,G57:T57,G52:T52,G47:T47,G42:T42,G37:T37,G32:T32,G27:T27,G22:T22,G17:T17G12=VLOOKUP(G9,DATA!$Z$3:$AB$38,3,FALSE)
E9,E14,E19,E24,E29,E34,E39,E44,E49,E54E9=COUNTIF(G9:T9,"*30*")
Cells with Data Validation
CellAllowCriteria
V6:AA7List=$G$2:$T$2
C13List=DATA!$X$3:$X$8
C58List=DATA!$X$3:$X$8
C53List=DATA!$X$3:$X$8
C48List=DATA!$X$3:$X$8
C43List=DATA!$X$3:$X$8
C38List=DATA!$X$3:$X$8
C33List=DATA!$X$3:$X$8
C28List=DATA!$X$3:$X$8
C23List=DATA!$X$3:$X$8
C18List=DATA!$X$3:$X$8
G19:T19List=DATA!$Z$3:$Z$31
G24:T24List=DATA!$Z$3:$Z$31
G29:T29List=DATA!$Z$3:$Z$31
G34:T34List=DATA!$Z$3:$Z$31
G39:T39List=DATA!$Z$3:$Z$31
G44:T44List=DATA!$Z$3:$Z$31
G49:T49List=DATA!$Z$3:$Z$31
G54:T54List=DATA!$Z$3:$Z$31
G14:T14List=DATA!$Z$3:$Z$31
G9:T9List=DATA!$Z$3:$Z$31
C1:E2List=DATA!$AD$3:$AD$4
A3:B8List=DATA!$T$3:$T$69
C11:D11List=DATA!$V$3:$V$25
C16:D16List=DATA!$V$3:$V$25
C21:D21List=DATA!$V$3:$V$25
C26:D26List=DATA!$V$3:$V$25
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hello, here is an attempt for further testing:

Excel Formula: 
=LET(
a,HSTACK($C$3:$F$3,SEQUENCE(,COLUMNS($G$3:$T$3),$A$3)),
b,FILTER($C$9:$T$58,$F$9:$F$58="Shift"),
c,HSTACK(FILTER(b,a=$V$6),CHOOSECOLS(b,1)),
XLOOKUP(V10,CHOOSECOLS(c,1),CHOOSECOLS(c,2),"N/R"))
 
Upvote 0
Solution
hagia_sofia said:
Hello, here is an attempt for further testing:

Excel Formula: 
=LET(
a,HSTACK($C$3:$F$3,SEQUENCE(,COLUMNS($G$3:$T$3),$A$3)),
b,FILTER($C$9:$T$58,$F$9:$F$58="Shift"),
c,HSTACK(FILTER(b,a=$V$6),CHOOSECOLS(b,1)),
XLOOKUP(V10,CHOOSECOLS(c,1),CHOOSECOLS(c,2),"N/R"))
Click to expand...
This is amazing! Thank you so much. Appreciate it
 
Upvote 0
hagia_sofia said:
Thanks for the feedback!
Click to expand...
No problem. I have realised I may need one more piece of functionality with this, but it may be a bridge too far.
Basically, wondering if it's possible that if there is data in the cell below the name that has been returned, that the value in the cell beneath is returned instead?
As per the attached image.


Realise this may be confusing things a little too much :ROFLMAO:

Thanks again for your help
Hayden
 

Attachments

  • Capture.jpg
    Capture.jpg
    105.9 KB · Views: 20
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VLOOKUP?
Replies
1
Views
222
MeaclH
M
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
125
MeaclH
M
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
516
Fluff
Fluff
M
  • Question Question
Help with FILTER & SPILL and formatting...
Replies
0
Views
77
MeaclH
M
J
  • Question Question
conditional formatting based on adjacent cells matching
Replies
1
Views
281
etaf
etaf

Forum statistics

Threads
1,224,830
Messages
6,181,227
Members
453,025
Latest member
Hannah_Pham93

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