Forum Discussion
Re: Xlookup and/or Index,Match Help
My apologize, I thought I uploaded an Excel document for reference.
4 Replies
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.
ok so the problem is that I lazily too the entire sheet/columns/rows and hence a way overkill and inefficient.
you can use TRIMRANGE (or select a smaller range) but you have to do the same for the NAME column so you could do something like this:
=LET(datecols,FILTER(TRIMRANGE('Work Schedule'!1:1048576,2,0),ISNUMBER(XMATCH('Work Schedule'!2:2,'Weekend Schedule'!D6:G6))),FILTER(HSTACK(CHOOSECOLS('Work Schedule'!1:.1048576,2),datecols),ISNUMBER(SEARCH('Weekend Schedule'!B9,BYROW(datecols,CONCAT)))))but the better thing to do would be to format the Work Schedule data as a Table (Home -> Format as a Table) and then if you leave the table name as Table1 it would look like this:
=LET(datecols,FILTER(Table1,ISNUMBER(XMATCH(Table1[#Headers],TEXT('Weekend Schedule'!D6:G6,"ddd, dd-mmm")))),FILTER(HSTACK(Table1[LAST NAME, FIRST NAME M.I.],datecols),ISNUMBER(SEARCH(B9,BYROW(datecols,CONCAT)))))i am attaching the updated sheet but note because I formatted the Work Schedule as a Table the date row was converted to text values which is why the formulas have the TEXT function in them.
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.
