SOLVED

Iron Contributor

# Unable to use a nested IF(AND) function in an array function (Discussion)

Hello there!

I came across the following discussion on the community:

https://techcommunity.microsoft.com/t5/excel/two-wrongs-don-t-make-a-right-negative-worker-values-ar...

Looking at the response by @Patrick2788, I was trying to play around with his proposed function. It was rather more intuitive to me to use a nested IF(AND) function and I was surprised it does not work for an array function.

I was just wondering if anyone knew why this happens and the logic behind this. Tested the IF(IF) function as well as a single IF(("logic1")*("logic2")) and both worked efficiently. Why wouldn't a nested IF(AND) function serve here?

Appreciate any kind response.

Martin

5 Replies
best response confirmed by MAngosto (Iron Contributor)
Solution

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

AND does not return an array of TRUE/FALSE values, but a single TRUE/FALSE value.

(logic1)*(logic2) returns an array of 1/0 values.

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

It's exactly as @Hans Vogelaar explained. I purposely wrote a nested IF because IF-AND won't spill (Unless you have some help from BYROW but nothing that sophisticated was needed for that task).

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

The and() returning a single value is annoying when dealing with arrays, but it can be worked around.

If it is a problem you have, here is one possible approach using a functional programming style (it allows chaining together multiple logical relationships, but that chain is not necessary here).  The important bit is that the two arrays entered must already be boolean values (TRUE or FALSE) or otherwise "truthy" (Excel will evaluate them as TRUE or FALSE).

``````=LET(
and_base, LAMBDA(array1_bool, LAMBDA(array2_bool, IF(array1_bool * array2_bool, TRUE, FALSE))),
and_func, LAMBDA(array1_bool, array2_bool, and_base(array1_bool)(array2_bool)),
array1_bool, {TRUE; TRUE; FALSE; FALSE},
array2_bool, {TRUE; FALSE; TRUE; FALSE},
and_func(array1_bool, array2_bool)
)``````

The easiest way to make arrays into booleans is to simply apply your criteria to the array, e.g.

``````=LET(
and_base, LAMBDA(array1_bool, LAMBDA(array2_bool, IF(array1_bool * array2_bool, TRUE, FALSE))),
and_func, LAMBDA(array1_bool, array2_bool, and_base(array1_bool)(array2_bool)),
array1, SEQUENCE(10),
array2, SEQUENCE(10),
array1_bool, array1 <= 6,
array2_bool, array2 >= 4,
and_array, and_func(array1_bool, array2_bool),
headers, {"Array 1", "Array 2", "Crit 1 and Crit 2 met"},
)``````

output should be:

 Array 1 Array 2 Crit 1 and Crit 2 met 1 1 FALSE 2 2 FALSE 3 3 FALSE 4 4 TRUE 5 5 TRUE 6 6 TRUE 7 7 FALSE 8 8 FALSE 9 9 FALSE 10 10 FALSE

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

Wow! I was initially looking for a technical explanation on why a nested AND function would not return an array when an IF-IF or an IF with "*" conditions does, but this workaround is definately interesting though.

Thanks for the contribution.

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

This is a guess, but I think “and” is an aggregate function like sum - no mater how many arguments you pass to it, it will return a single value. Think of it like a straight Boolean check (are truth values a, b, c, … z all true?) rather than like a an expanded group of truthy comparisons. “And” sees only an array of truth values, no matter how that array is composed.

But that is a guess - I have no idea about how excel works under the hood.
1 best response

Accepted Solutions
best response confirmed by MAngosto (Iron Contributor)
Solution

# Re: Unable to use a nested IF(AND) function in an array function (Discussion)

AND does not return an array of TRUE/FALSE values, but a single TRUE/FALSE value.

(logic1)*(logic2) returns an array of 1/0 values.