Forum Discussion
Solved
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...
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
-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