Forum Discussion
Xlookup and/or Index,Match Help
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
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
Thank for the response. That formula does what I need it to do but I cannot get it to run when I transfer it to my product. I keep getting the "ran out of resources" error. I believe it has to deal with the first FILTER function. I would assume that the 1:1048576 are the rows. I've tried to reduce the range that it's filtering and I'll either get the #CALC or #VALUE errors.
Even on a blank excel, you can tell it struggles.