Skip to content

Differences between `=IF()` and `=SWITCH()` Functions

Learn when you should use =IF() function in a formula and when you should use =SWITCH() function instead. The section explains the difference through two examples.

The Differences

Here is an explanation of the differences between =IF() and =SWITCH().

  • The =IF() function accepts multiple nested condition as first parameter. This enables to chain sets of conditions. Also, you can nest further conditions as second and third parameter.
  • The =SWITCH() function takes a field's value and compare it with a series of cases. According to each case, the function yields a different value as result.

When to Use =IF() or =SWITCH()

If you have two or more conditions to be verified as first parameter and the result is another formula to be executed, use =IF().

If you have only one value to be compared with a series of values as conditions, use =SWITCH()

Example for =SWITCH()

Consider a case when you have a Lookup drop-down list and a Multiselection-type field.

The following formula fills the Multiselection field's cell with a set of values according to the option's Id chosen from the drop-down list.

You don't need the =IF() in this scenario: the =SWITCH() function is shorter and easier to employ.

Example

1
2
3
4
5
6
7
8
// The formula involves only one value to be compared.
=SWITCH(
    $CURRENTSTORAGE.lookupDropdownlist; 
    =CASE(2; =LIST(1;2;3));
    =CASES(=LIST(3;4); =LIST(4;6));
    =CASE(5; =LIST(5));
    =DEFAULT(=LIST(7))
)

In this case, you compare one value ($CURRENTSTORAGE.lookupDropdownlist) with the values in the =CASE() or =CASES() conditions. The formula yields the value in =DEFAULT() if the first parameter's value doesn't match any =CASE() or =CASES() conditions.

You could do the same thing using the =IF() function and it would work, but you must write a longer formula with nested conditions:

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
=IF(
    =EQ($CURRENTSTORAGE.lookupDropdownlist; 2); 
    =LIST(1;2;3);
    =IF(
        =OR(
            =EQ($CURRENTSTORAGE.lookupDropdownlist; 3); 
            =EQ($CURRENTSTORAGE.lookupDropdownlist; 4)
        );
        =LIST(4;6);
        =IF(
            =EQ($CURRENTSTORAGE.lookupDropdownlist; 5); 
            =LIST(5);
            =LIST(7)
        )
    )
)

=SWITCH() expresses the same conditions of the formula above in a more concise and efficient way.

Example for =IF()

Consider a case when the formula fills a String-type calculated field's value. In the example, the formula updates a cell in an HR application managing new employees.

The formula checks if all the following conditions are satisfied:

  • The Lookup drop-down list has recorded an status different from the option with the Id 5.
  • The user belongs to the User Group "HR"
  • The Boolean field "IsRegistered" value is true.

According to the first parameter's result:

  • If all the first parameter's conditions are satisfied, the formula records in the cell who checked the row and when.
  • If the first parameter's conditions aren't satisfied, the formula records in the cell that no one has checked the row yet.

You need the =IF() since there are multiple conditions to be checked for different values.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
=IF(
    =AND(
        =NEQ($CURRENTSTORAGE.LookupUserStatus; 5);
        =EQ($CURRENTSTORAGE.IsRegistered; $TRUE);
        =CONTAINS($CURRENTUSER.GROUPNAMES; "HR");
    ); // The first parameter's formula checks three values.
    =CONCAT(
        $CURRENTUSER.NAME; 
        " verified in Date "; 
        =FORMATDATE($CURRENTDATE; "dd/MM/yyy HH:mm:ss");
    );
    "Employee not checked yet."
)

You can't do that with the =SWITCH(). The =SWITCH() is useful when the formula compares a single field's value. If there are two or more conditions checking two or more values, you need the =IF().