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