Mar 31 2022 03:37 PM
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
Apr 02 2022 06:06 PM
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.
Apr 03 2022 08:29 AM
Apr 03 2022 12:00 PM
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) )
Apr 03 2022 01:55 PM
@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.