How to use ArrayFormula in Google Sheets, with multiple IF conditions?

2
votes

So I just found out about ArrayFormula and am trying to convert my spreadsheet to utilize it, to be more efficient. Its worked wonderfully on all my columns except one, which is giving me trouble.

Currently, I have this formula in it (and copied to each cell):

=IF(C2="Immediate",
  D2+1,
  IF(C2="3 Day",
    WORKDAY(D2,3,Holidays!$B$2:$B$11),
    IF(C2="5 Day",
      WORKDAY(D2,5,Holidays!$B$2:$B$11),
      IF(ISBLANK(C2),
        IFERROR(1/0)
      )
    )
  )
)

It works wonderfully to calculate a date, based on a dropdown menu I have in another column. I cant figure out how to convert this to utilize ArrayFormula, as I get an error as soon as I add a second IF statement.

I can get the first statement to work with this formula: 

=ARRAYFORMULA(IF(C2:C="Immediate",D2:D+1,))

But as soon as I try to add a second IF statement(such as with the formula below) I get an error.

How do I do this? I figure its probably simple but I cant figure it out!

Im trying to use a simpler formula to figure out where I am going wrong, and same thing will happen with the following formula so Im clearly doing something wrong! 

=ARRAYFORMULA(IF(A1:A=1,"Hello",),IF(A1:A=2,"Goodbye",))

With only the first IF statement, it works. As soon as I add the second, I get #N/A

4
google-sheetsarray-formulas

4 Answers

2
votes

I know this is old but maybe try this formula:

={"Header_Cell_Name_Here";
  ArrayFormula(
    IFS(
      $C$2:$C="","",
      $C$2:$C="Immediate", $D$2:$D + 1,
      $C$2:$C="3 Day", WORKDAY($D$2:$D,3,Holidays!$B$2:$B$11),
      $C$2:$C="5 Day", WORKDAY($D$2:$D,5,Holidays!$B$2:$B$11)
    )
  )
}

IFS is a great alternative to IF. Rather than taking three arguments like you do with an IF statement:

IF(logical_expression, value_if_true, value_if_false)

an IFS statement can handle any number of conditions:

IFS(condition1, value1, [condition2, ...], [value2, ...])
1
votes

Try this formula somewhere on row 1 of your sheet:

=ARRAYFORMULA(IF(A:A="",,IF(A:A=1,"Hello",IF(A:A=2,"Goodbye","Other Result"))))

0
votes

you can use that formula

=ArrayFormula(IF(C2:C="",IFERROR(1/0),IF(C2:C="Immediate",D2:D+1,IF(C2:C="3 Day",WORKDAY(D2:D,3,Holidays!$B$2:$B$11),IF(C2:C="5 Day",WORKDAY(D2:D,5,Holidays!$B$2:$B$11),IF(ISBLANK(C2:C),IFERROR(1/0)))))))

here is an example

0
votes

Your first formula, expanded to arrays, works perfectly well for me

=ARRAYFORMULA(IF(C2:C5="Immediate",
                 D2:D5+1,
                 IF(C2:C5="3 Day",
                    WORKDAY(D2:D5,3,Sheet2!$B$2:$B$11),
                    IF(C2:C5="5 Day",
                       WORKDAY(D2:D5,5,Sheet2!$B$2:$B$11),
                       IF(ISBLANK(C2:C5),
                          IFERROR(1/0)
                       )
                    )
                 )
              )
 )