Can't pass a range to a lambda using Let and Makearray functions-CodePudding

Referring back to

This very similar formula works correctly (it isn't supposed to solve the original problem, but is just included to test whether a range can be passed into a lambda inside a Let statement):

=LET(
    range, Sheet1!A1:D2,
    SCAN(0, range, LAMBDA(a, c, a   c   INDEX(range, 1, 1)))
)

The same code also works passing range as a parameter into a named function in Google Sheets.

Defining range as a named range also works.

Using an enclosing Lambda instead of Let has the same issue.

Can there be a specific issue with Makearray or is there another reason why this fails?

CodePudding user response：

The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like:

=LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c))))))

This would resemble the construct of your inital formula but undoubtedly the formula suggested by Jos Wooley in the linked question would be prefered.

Also, in the linked question you have mentioned in your own answer that you'd like to use SCAN(). However, you have noticed that this nesting lambda helper functions will result in an error. I just wanted to show you that it is possible with just a single SCAN() function:

=SCAN(0,A1:D2,LAMBDA(a,b,IF(ISERROR(OFFSET(b,0,-1)),b,a b)))

But yes, that's volatile. I have been puzzling a bit and found a more verbose non-volatile version too. I noticed that we could compare the row/column of the 'b' variable inside the lambda structure. I did find a way to do this non-volatile however it got quite lengthy so went with the above. Who knows if a little more puzzling gets me a non-volatile version.

It does feel a bit 'glitchy' but since the error due to OFFSET() will only occur on each and every value in the 1st column of the array we could actually use some sort of variation to the BYROW() helper inside SCAN() and do these calculation for each row of the intial array seperately.