Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Mar 31, 2022

Lambda Function returning multiple items

Hello all. In the attached workbook I created 2 lambda functions to try and find 'near' match results.  basically it will look at the lookup value and keep adding characters to filter the results do...
excel
mtarler's avatar
mtarler
Silver Contributor
Apr 03, 2022
OK so maybe I should have known better. Basically FILTER() returns an array even if that array is only 1 item it is still an array object. Therefore the statement: IF(nFind<>0,... was being treated as an IF of an array and hence as a function (and not just an operator) and therefore evaluating BOTH the true and false arguments. What was throwing me was that because the FALSE argument was an array of X components it was forcing the TRUE argument that was a single element to be an array of the same length AND because it was a single element it repeated that value to fill the array. IF for example it was already an array like {1,2} and the other argument was {5,6,7,8} then the output would be {1,2,#N/A,#N/A}. Here is an example:
=LET(a,FILTER({2,0},{1,0}),IF((ROWS(a)=1)*(a>0),{2,4},{3,3,3,3}))
The parameter 'a' is clearly a single value but treated as an array and the result is {2,4,#N/A,#N/A}. If {2,4} was instead a single value then that single value is repeated 4x.
Maybe others already know this behavior but I wish it was better documented.
Lastly, the easiest solution for me was once I knew it was a single element (i.e. the second IF() statement) to insert INDEX(..., 1) to essentially convert the Array of 1 to a single Element
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 03, 2022

mtarler 

Yes, FILTER returns an array before we landed result into the grid

=TYPE( FILTER(1, 1) )

returns 64.

 

Interesting finding with IF.  In general if condition is an array any function which uses such works specifically. Close to your example

=IF(     {1}, {2,4}, SEQUENCE(,4) )
=CHOOSE( {1}, {2,4}, SEQUENCE(,4) )
=IFS(    {1}, {2,4}, TRUE, SEQUENCE(,4) )
etc

Change {1} on 1 and they work as expected.

 

It looks like function reserve max available for all components array size. Apply logic to applicable elements of it. Returns to grid entire array without dropping unused elements. Here we could see that better:

=IF( {1}, {2,4;3,5}, SEQUENCE(7,8) )

But that's condition form one element. How logic is applied for more complex condition I could understand with CHOOSE, IF is more complex for me

=IF( {1,0}, {2,4;3,5}, SEQUENCE(7,8) )
  • mtarler's avatar
    mtarler
    Silver Contributor
    Apr 03, 2022

    SergeiBaklan Ah yes, as usual you do a splendid job filtering down to essence.  I particularly like you example: 

    =IF( {1}, {2,4;3,5}, SEQUENCE(7,8) )

    as for the second example it would appear IF acts exactly like CHOOSE except for IF only having 2 options and them being ref as 1,0 instead of CHOOSE having many more potential options 1,2,3,....

    In particular

    =IF( {1,0}, {2,4;3,5}, SEQUENCE(7,8) )

    appears to be same output as

    =CHOOSE( {1,2}, {2,4;3,5}, SEQUENCE(7,8) )

    and in each case it appears to 'fill' the column (7 rows) based on the single value available in that column.  If you use a 2d array in the conditional argument:

    =IF( {1,0;0,1}, {2,4;3,5}, SEQUENCE(7,8) )

    then it will only populate that 2x2 grid and the rest are N/A.

    Be it Intentional or unintentional 'feature', I guess we are stuck with it and just need to be aware.

     

     

Resources