Lambda Function returning multiple items

Silver Contributor

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 down to least number of matches.   So if you have "Digital Systems" and on the lookup list you have "Digits are us" and "Digital Sys" it finds more matching characters to Digital Sys and will return that value.  It seems to work most of the time.

I don't know how useful these Lambda functions will actually be but I am completely baffled why in some cases they are returning duplicate values.  So in the above it might return "Digital Sys" multiple times.  But only sometimes does that happen.  I've tried breaking my steps down and just can't figure out what step(s) are causing an array output that would cause this sort of result.

Although better/more efficient versions are always welcome, I am really interested in learning what I did 'wrong' to cause these duplicates to appear.

Thank you 

4 Replies

OK so I think I found the 'problem' which if I'm right is very concerning to me.  Here is the Lambda function:

=LAMBDA(val, range, [min_letters],
    LET(m_l, IF(ISOMITTED(min_letters), 2, min_letters),
        IF(m_l < LEN(val),
           LET(nFind, nearmatches2(val, range, m_l + 1),
               IF(ROWS(nFind) > 1,
                  nFind,
                  IF(nFind <> 0,
                     nFind,
                     FILTER(range,
                            IFERROR(SEARCH(LEFT(val, m_l), range), 0),
                            0)
                    )
                 )
              ),
           FILTER(range, IFERROR(SEARCH(val, range), 0), 0)
           )
       )
)

so basically it will start by recursively calling itself until it reaches the full length of the 'val' parameter (IF statement on line 3 is TRUE so inside the LET on line 4 it calls itself)

Then the full length search of 'val' in 'range' happens and returns the list of values OR 0

The recursive calls then unroll and if 1 or more values were previously found then just return them otherwise do another FILTER at the level of characters.

Notice that I 1st check IF the returned value ('nFind') has more than 1 row (i.e. an array) so if so it shouldn't go any further

BUT what I'm finding is that the final answer IS becoming an array based on the number of times the smallest character set for 'val' is found.  So if I initially set min_letters to 3 and there are 5 results with those initial 3 characters but the full 'val' is found 1x it will return that correct value 5x.

This means (to me) that inside this Lambda the IF statement is acting like a function and not an operator and even though the result was true it is still evaluating the false condition and forcing that variable to be an 'n' length array.  I already posted about this behavior with the IFS() function learned the difference between IF and IFS and some other functions (thank you @Joe User ) but apparently switching back to the IF() isn't necessarily the solution either.  

 

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

@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) )

@Sergei Baklan 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.