Excel: AND function with structured references

Question

When using Excel structured references, Assume this table (MyTable):

If I enter the following in a cell, I get 5 values: =MyTable[Col A]>2

...and if I enter the following in another cell, I get 5 values: =MyTable[Col B]<9

However, If I want to test both conditions in a logical AND and enter the following in a cell: =AND(MyTable[Col A]>2, MyTable[Col B]<9)

...I get only one result (FALSE) becuase the AND function assesses all 10 logical expressions and returns a single value.

How do I get Excel to return 5 values, one for each row?

I've thought about using BYROW with a LAMBDA, but can't figure out how to write a suitable LAMBDA.

Answer 1

Try the following formula-

=(MyTable[Col A]>2)*(MyTable[Col B]<9)

If you want to use LAMBDA() formula then go with MAP().

=MAP(MyTable[Col A],MyTable[Col B],LAMBDA(x,y,AND(x>2,y<9)))