Evaluation result changes when using ARRAYFORMULA format
I'd like to use the following formula with ARRAYFORMULA:
=IF(IFERROR(OR(VLOOKUP($A2, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", ""))
Using this formula and dragging it down works perfectly fine; all values evaluate correctly.
However, when I make the required changes to use it with ARRAYFORMULA, the first if condition behaves differently.
The evaluation for the first row A2 switches from TRUE to FALSE.
What could be the cause for this?
I have created a test sheet replicating the problem I am having:
https://docs.google.com/spreadsheets/d/1MuBstN9tqVccSExTnI7GOQxfu9OK-nqgyoTHq_pJqYE/edit?usp=sharing
Appreciate any help I can get with this!
Try:
=ArrayFormula(IF(IFERROR(IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE"), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP($A2:A, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))
Result:
Explanation:
The reason why your formula was not working is because using the OR with arrayformula has different behavior. You can replace it with "+", this will return either 1 or 0. Then use IF condition to make it return TRUE if 1, and FALSE if 0. See image below for differences:
From:
OR(VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE), VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE))
To:
IF((VLOOKUP($A2:A, DATA!$A$2:D, 3, FALSE)+ VLOOKUP($A2:A, DATA!$A$2:D, 4, FALSE)=1),"TRUE","FALSE")
You may also refer to this reference video I have found for more details: How to use AND / OR with ARRAYFORMULA on Google Sheets? | Sheet Hacker