Search code examples
excellambdaexcel-formula

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.

Solution

  • 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.