Forum Discussion
Look Up and Dynamic Array
Hello again,
I'm not an advanced user but am familiar with bunch of excel formulas.
I want to dynamically retrieve all the values from a table field. After doing some research I have come to think that it's possible with array formulas.
However, I wasn't able to get it done.
Basically there is a table with employee names and I want to get all of their names based on a shift hour which are also listed in the table in a index match or xlookup friendly format. The table will be dynamic so I want my formula to be dynamic as well that's why I thought I'd need an array formula.
This is my formula but I'm getting an error. I'm thinking I'm doing a logical error with my cube formulas.
{=CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Shift].["&$M$2&"]"),CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Name]"))}I've also tried index and match and xlookup but failed again.
{=INDEX(Regulated[Name],MATCH($M$2,Regulated[Shift],0))}{=XLOOKUP($M$2,Regulated[Shift],Regulated[Name])}
I also want to note that there are blank cells in between employee names which is unavoidable due to the structure of the table requirements.
{=Regulated[Name]}This simple formula retrieves all the employees from all the shifts. However, what I need it to do is exclude blank cells and be dynamic depending on the shift hour I select from a drop down list on M2.
I'm attaching a sample workbook to demonstrate what I need.
I want to express that the table is neatly formatted because it helps me visualize things better. Queue Assignment Mid Sheet is how the table formatted in default.
I'd really appreciate your assistance.
Thanks.
kheldar Well maybe here is another function for you to learn 🙂 FILTER(). It is new with the introduction of dynamic arrays. There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.
=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")note I had to add the TEXT() to your M2 because the shift time is converted to text. I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.
7 Replies
kheldar Well maybe here is another function for you to learn 🙂 FILTER(). It is new with the introduction of dynamic arrays. There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.
=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")note I had to add the TEXT() to your M2 because the shift time is converted to text. I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.
Can I bother you with something related with filter function? After researching a bit I've learnt that filter doesn't natively work with dynamic cell references #.
=XLOOKUP(A2#&"Meal Break",RawMMP[Name]&RawMMP[Break],RawMMP[Minutes])This the alternative I'm using.
However,
This does not work:
=FILTER(RawMMP[Duration1],RawMMP[Name]=A2#,"na"),
Is it possible to make filter behave as such?- So your example of won't work is correct, it won't work because you are doing a comparison of a column of names to an array of values (A2#). I'm trying to understand the relationship between the Filter example and the Xlookup example and not sure how the values translate but a couple of points.
-When possible (i.e. when you want to return a single value/array) try to use XLOOKUP as it will probably be faster/less burden on Excel.
- Based on the xlookup example it looks like you want multiple conditions so to do that in Filter use * to AND conditions or + to OR conditions:
-FILTER( [table/array], ([column a]="value1") * ([column b]="value2") + ([column c]="value3") , "na" )
This will return all the rows from [table/array] where the corresponding values in a & b = value1 & value2 respectively OR the corresponding value in column c is value3
- Wow! That worked like a charm! Thanks a lot!
