Excel: Lambda unexpectedly captures whole array context with OR.

Question

This is a reduced case:

=LET(
items, {"aaa",123},
tc, LAMBDA(txtA,txtB,
txtA & " " & txtB & " a: " & ISNUMBER(txtA) & " b: " & ISNUMBER(txtB) & " or: " & OR(ISNUMBER(txtA), ISNUMBER(txtB))
),
TRANSPOSE(tc(items, "bbb") )
)

The expected behaviour would be that the lambda is called independently for each entry in "items". Instead, it mostly acts that way, but the OR can get the whole context!

aaa bbb a: FALSE b: FALSE or: TRUE 123 bbb a: TRUE b: FALSE or: TRUE

The first OR statement (aaa, bbb) evaluates to TRUE even though the individual items both evaluate to FALSE.

We can confirm that this is caused by evaluating the whole context by swapping the 123 for "ccc".

aaa bbb a: FALSE b: FALSE or: FALSE ccc bbb a: FALSE b: FALSE or: FALSE

Now OR on (aaa, bbb) evaluates FALSE, even though we didn't change those items!

---

In the non-reduced version, the OR is even in a separate, defined name LAMBDA.

1. Is there a way to make sure a defined LAMBDA won't suddenly switch context?
2. If not, is there a general way to tell which functions are unsafe?
3. What is the replacement for the OR function that will operate only on the current context and not expand?

Answer 1

Maybe....

=LET(

items, {"aaa",123},

tc, LAMBDA(txtA,txtB,

txtA & " " & txtB & " a: " & ISNUMBER(txtA) & " b: " & ISNUMBER(txtB) & " or: " & IF(ISNUMBER(txtA)+ISNUMBER(txtB),TRUE,FALSE)

),

TRANSPOSE(tc(items, "bbb") )

)

Answer 2

...The expected behaviour would be that the lambda is called independently for each entry in "items".

As a side note, you are correct. The 'or' function does thread itself over array inputs in other languages. However, in Excel, boolean functions return 1 value for the whole array.

To do more advanced programming in Excel, one would thread the "or" function over each input.

So, in general...

=LET(
  V, ISNUMBER({"aa";123}), 
  K, ISNUMBER("bb"), 
  MAP(V, LAMBDA(b, OR(b, K)))
)