EXCEL - Strange behaviour with LAMBDA function

Question

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;

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.

JvdV · Accepted Answer · 2023-01-26 11:30:33Z

3

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.

answered Jan 26, 2023 at 11:30

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rkr87 Over a year ago

Hi, this didn't quite work for my requirements as TOCOL doesn't filter out non-empty cells with a blank value ="" which my data contains, however, it did really help me in simplifying my function without any problems with the calculation being wrapped in rows. Thanks! Final solution: =LAMBDA(range,[ignore_header],DROP(FILTER(range,range<>""),ignore_header)))

Collectives™ on Stack Overflow

EXCEL - Strange behaviour with LAMBDA function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related