Forum Discussion
Look Up and Dynamic Array
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.
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 The problem is that both RawMMP[Name] and A2# are arrays. The first problem is that the result of such a comparison is ambiguous. If we assume that each element of the spilt array A2# is to be compared at a time, the result would be an array of overlapping filtered ranges, so still no use.
You either need to write a separate formula for each element of A2# and space them out, or you turn to more advance programming techniques.
= MAP(distinctName#, LAMBDA(name, TEXTJOIN(", ",, FILTER(RawMMP[Duration1],RawMMP[Name]=name,"na") ) ) )This takes each element of A2# (I have assumed it to be a list of distinct names) and produces a filtered array. I used TEXTJOIN to turn the filter array result into a comma-separated list. MAP brings the lists together as an array of lists.
To put the result together as a composite list required advanced techniques which I have already discussed today within another post.
- Thank you for your response. Is Lambda available for use outside of Insider channel now?
- Wow! That worked like a charm! Thanks a lot!
