0

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!

George
  • 1,196
  • 1
  • 2
  • 10
dan
  • 347
  • 2
  • 14

3 Answers3

1

You can use BYROW or MAP to scan your whole range:

=BYROW(A2:A,LAMBDA(each,IF(each="","",IF(IFERROR(OR(VLOOKUP(each, DATA!$A$2:D, 3, FALSE), VLOOKUP(each, DATA!$A$2:D, 4, FALSE)), FALSE), "SENT", IF(IFERROR(NOT(ISNA(VLOOKUP(each, REJECTED!$A$2:A, 1, FALSE))), FALSE), "REJECTED", "")))))
Martín
  • 7,849
  • 2
  • 3
  • 13
  • Hi, thanks! I've never seen BYROW or LAMBDA before, could you provide a quick summary of how the overall formula works? This could be useful for anyone else finding this question as well. – dan Mar 23 '23 at 01:57
  • And perhaps more importantly, what exactly is wrong with my approach? – dan Mar 23 '23 at 01:58
  • 1
    BYROW and LAMBDA are used together. What you're doing is iterating your formula through a range "row by row". Basically, I'm saying "take this range A2:A, and for each row apply this formula" and with LAMBDA you're naming the variable where that range is inserted in your formula. I've actually not made any modifications to your prior formula, just inserted it in BYROW – Martín Mar 23 '23 at 03:26
  • Very interesting, I will definitely be using these two functions moving forward in other applications, thanks! – dan Mar 28 '23 at 09:52
1

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: enter image description here

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: enter image description here

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

Logan
  • 1,691
  • 1
  • 4
  • 11
1

I think you can simplify the formula. Try-

=MAP(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,IF(ISERROR(OR(FILTER(DATA!C2:D,DATA!A2:A=x))),
IF(ISNUMBER(XMATCH(x,REJECTED!A2:A)),"REJECTED",""),"SENT")))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36