What prevents a lambda function from evaluating on an array?

Question

I thought that a lambda function defined for scalar input values would automatically iterate through an array when faced with an array input, but it seems that it's not universally true. I want to understand why, because I'm not happy with the definition I ended up with to make it work.

There is a range of values and I want to test each cell for multiple conditions. E.g. check whether an integer values is greater than min and less then max.

Using the AND function I ended up with the following lambda:

=LAMBDA(x;AND(x>2;x<10))

however, this definition won't work when used as an array function, only when applied on each individual cell separately.

I have tried a different approach and defined the lambda purely as a numerical expression:

=LAMBDA(x;(x>2)*(x<10)=1)

this definition works on an array, but I find it generally less readable.

Why does the definition with AND not work?

sample workbook

Answer 1

I think that your problem stems from the misunderstanding on how LAMBDA works. Lambda is a custom function that is used by predefined function. That predefined function iterate thru an array and applies the LAMBDA function to each element of iteration. For example, if you have a 2D array, that predefined function can take each column to get a result per each column. Or it can take each element of the table to get another table as a result.

In your case, you'd want to iterate thru each row of your table to get a column of results. That predefined function in your case is BYROW.

Therefore, the correct LAMBDA formula for your situation is:

=BYROW(A2:A6,LAMBDA(x,
    AND(x>2,x<10)))

The function AND evaluates the array of all individual results. To get the function applied to each element, in this case, a row, you have to use another LAMBDA function.

I suggest watching this video. Is the best LAMBDA tutorial I know:
Excel is Fun: All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples!

Enjoy!