CODEWITHSUNDEEP
excellambdaexcel-formula
rkr87
rkr87

Reputation: 181

EXCEL - Strange behaviour with LAMBDA function

I have the below LAMBDA function created as a named range (LIST_VALUES);

=LAMBDA(range,[ignore_header],LET(filtered,FILTER(range,range<>""),IF(OR(ISOMITTED(ignore_header),NOT(ignore_header)),filtered,INDEX(filtered,SEQUENCE(ROWS(filtered)-1,,2)))))

The function itself works exactly as intended, however, when I wrap the function with ROWS() I get inconsistent behaviour when the ignore_header flag is set to TRUE;

enter image description here

The above example highlights the issue, cell F8 should equal "3", the same as D8.

D8

=ROWS(LAMBDA(range,[ignore_header],LET(filtered,FILTER(range,range<>""),IF(OR(ISOMITTED(ignore_header),NOT(ignore_header)),filtered,INDEX(filtered,SEQUENCE(ROWS(filtered)-1,,2)))))($A:$A,TRUE))

F8

=ROWS(LIST_VALUES($A:$A,TRUE))

Does anyone know what going on here or how I fix it? I'm currently working around the issue by setting ignore_header to false and deducting 1 from the result, which works fine, but I'd really like to understand what's going on here.

Upvotes: 2

Views: 185

Answers (1)

JvdV
JvdV

Reputation: 76000

We can't really step through the internal process within the lambda, so it's difficult to pinpoint where the error would surface (hence the count of '1' for an internal error). But may I suggest to simplify your formula to:

=LAMBDA(range,[ignore_header],DROP(TOCOL(range,3),OR(ISOMITTED(ignore_header),NOT(ignore_header))))

I can't detect any inconsistency when wrapped in ROWS(), eg.: =ROWS(LIST_VALUES(A:A,TRUE)) worked as expected for me now.

Upvotes: 3

Related Questions