Forum Discussion
Xlookup and/or Index,Match Help
Greatings and salutations,
I have been working on a product, and struggling, for our work schedule that would allow me to put in a start date on the "Weekend Schedule" sheet and automattically pull the names from the "Work Schedule" sheet if they are assigned for "W/D-P" or "W/D-A" within the matching date range on the "Weekend Scheulde"
Right now I can get it the "Weekend Schedule" to populate if I already know the name and manually input their name into the table. But I would like to have 'Weekend Schedule'!D7:G15' & 'Weekend Schedule'!D17:G25' auto update if the corresponding dates in 'Work Schedule' have "W/D-P" & "W/D-A" respectively.
I have been unable to find anything via Googling and YouTubing. I am starting to believe this is impossible and would need sometype of VBA script for it. Any help/advice would be greatly appreciated.
maybe this is overkill:
=LET(datecols,FILTER('Work Schedule'!1:1048576,ISNUMBER(XMATCH('Work Schedule'!2:2,'Weekend Schedule'!D6:G6))), FILTER(HSTACK(Names,datecols),ISNUMBER(SEARCH('Weekend Schedule'!B9,BYROW(datecols,CONCAT)))))so i defined a name called Names for column B
line 1 finds the datecolumns based on the ones used on the sheet
line 2 then combines the names and those columns and then filters based on finding those values.
note there is a potential error if the same person has both "W/D-P" & "W/D-A" in those dates it will show both
7 Replies
My apologize, I thought I uploaded an Excel document for reference.
maybe this is overkill:
=LET(datecols,FILTER('Work Schedule'!1:1048576,ISNUMBER(XMATCH('Work Schedule'!2:2,'Weekend Schedule'!D6:G6))), FILTER(HSTACK(Names,datecols),ISNUMBER(SEARCH('Weekend Schedule'!B9,BYROW(datecols,CONCAT)))))so i defined a name called Names for column B
line 1 finds the datecolumns based on the ones used on the sheet
line 2 then combines the names and those columns and then filters based on finding those values.
note there is a potential error if the same person has both "W/D-P" & "W/D-A" in those dates it will show both
This is does exactly what I thinking. The only down side is it does slow down the overall product. I believe it has to do with the first filter. I might be mistaken but I the "1:1048576" the number of columns it's filtering? I've tried to lower the number but I always get the "#CALC" or "#VALUE" errors.
I'll continue to play around with it to see if I can find a way to simplify it. Again thank you for the assistance and your time.
It sounds like you might want to check FILTER(). Something like this maybe:
=FILTER( [names], [schedule dates]="W/D-P", "")
Thank you for the tip. I haven't messed with the filter function yet. I'll play around with it to see if I can use it for a final solution.
