Insert element to an array then use ARRAYTOTEXT function

[gifariz]

Previously I had similar problem with this one, where I needed to assign a value to array's element then use ARRAYTOTEXT function.
The reason I use arraytotext function is because I need to do iteration involving dynamic array variables, so I store the arrays in some cells in a row for each iteration and iterate downward.
So this time I made custom function: InsertArray=LAMBDA(array,i,val,LET(ib,SEQUENCE(ROWS(array)+1),IF(ib<i,INDEX(array,ib),IF(ib>i,INDEX(array,ib-1),val))))
The custom function works, but the output is again incompatible with arraytotext function where it gives me incorrect result for following case:
A={1;2}
B=InsertArray(A,3,3)={1;2;3}
C=ARRAYTOTEXT(InsertArray(A,3,3),1)='{1;1;3}' < It should be '{1;2;3}' >
Previously, I had to remove VSTACK in custom function and it works, this time I don't know what else can I try to make it work?

 

[JEC]

Pretty weird for sure. When I use the formula outside the name manager it works.... But you have to do this

=ARRAYTOTEXT(InsertArray(A,3,3)+0,1)

 

[JEC]

After looking a bit further it turns out that the lambda should be like this

=LAMBDA(array,i,val,LET(ib,SEQUENCE(ROWS(array)+1),IF(ib<i,ib,IF(ib>i,INDEX(array,ib-1),val))))

 

[gifariz]

After looking a bit further it turns out that the lambda should be like this

=LAMBDA(array,i,val,LET(ib,SEQUENCE(ROWS(array)+1),IF(ib<i,ib,IF(ib>i,INDEX(array,ib-1),val))))

oh wait, why the IF ib<i=true then value is ib, not INDEX(array,ib)?

 

[gifariz]

You know what, I will just make my own function to compress array: ArrayText=LAMBDA(array,"{"&TEXTJOIN(";",FALSE,array)&"}")
But it's still good to know if we can solve or know more about above issue

 

[djclements]

This appears to be more of an issue with ARRAYTOTEXT and how it interacts with the INDEX function, although it's difficult to explain why it works properly when used directly in the worksheet but fails when defined as a custom function in Name Manager. Even with the following simplified version...

=LAMBDA(array,i,val,LET(ib,SEQUENCE(ROWS(array)+1),IF(ib=i,val,INDEX(array,ib-(ib>i),1))))

...which uses a single IF statement with a single INDEX function that explicitly references the column_num, you would still need to put a plus sign before the InsertArray function when nesting it within ARRAYTOTEXT. For example:

=ARRAYTOTEXT(+InsertArray({"A";"B"},3,"C"),1)

This is similar to the behavior of some other functions like QUOTIENT, EOMONTH, etc. but for seemingly different reasons. With those function, the plus sign is needed to convert a range of values to an array, which is not the case here. Also, when InsertArray is used on its own (not nested within ARRAYTOTEXT), it returns the correct results in a spilled array, so it's very difficult to diagnose and explain.

Having said that, if we CHOOSEROWS instead of INDEX (as well as EXPAND to ensure the row_nums are within scope), it works as expected:

=LAMBDA(array,i,val,LET(n,ROWS(array)+1,ib,SEQUENCE(n),IF(ib=i,val,CHOOSEROWS(EXPAND(array,n,,val),ib-(ib>i)))))

Alternatively, you could also use SORTBY with EXPAND to get the same results:

=LAMBDA(array,i,val,LET(n,ROWS(array),SORTBY(EXPAND(array,n+1,,val),EXPAND(SEQUENCE(n),n+1,,i-1))))

If hope that helps. Cheers!

 

[djclements]

Alternatively, you could also use SORTBY with EXPAND to get the same results:

=LAMBDA(array,i,val,LET(n,ROWS(array),SORTBY(EXPAND(array,n+1,,val),EXPAND(SEQUENCE(n),n+1,,i-1))))

VSTACK might actually be a better choice than EXPAND here. It would be shorter and would allow multiple values to be inserted into the array at once. For example:

InsertArray:

=LAMBDA(array,i,val,SORTBY(VSTACK(val,array),VSTACK(i,SEQUENCE(ROWS(array)))))

=ARRAYTOTEXT(InsertArray({1;5;9},{2;3},{3;7}),1)

Returns: {1;3;5;7;9}