An alternative to IF statements using SWITCH()

SWITCH() is one of Airtable’s most recent additions to the formula field, and it can be a big time-saver when you’re working with conditional-heavy formulas (e.g. nested IF statements).

To demonstrate the difference between using SWITCH() and IF(), we’ll use the example of a formula that tells us the phase a project is in based on how close the due date is. The criteria we’ll use are:

• 4 weeks out from due date = planning phase
• 3 weeks out from due date = execution phase
• 2 weeks out from due date = loose ends and review phase
• 1 week out from due date = launch phase
• None of the above = out of range

For the sake of simplicity, let’s assume that we already have a field called “weeks until deadline” that provides us with the number of weeks until the project’s deadline.

Nested IF() statement approach

Using the formula below results in a different project phase depending on the {Weeks Until Deadline} field.

IF(
  {Weeks Until Deadline} = 4, 'Planning',
    IF(
      {Weeks Until Deadline} = 3, 'Execution',
      IF(
        {Weeks Until Deadline} = 2, 'Loose ends and review',
          IF(
            {Weeks Until Deadline} = 1, 'Launch', 'Out of range'
          )
      )
   )
) 

SWITCH() function approach

Similarly, the SWITCH() statement below produces the same result, but with a much cleaner formula. Notice that {Weeks Until Deadline} is only referenced a single time (as opposed to four times).

SWITCH(
 {Weeks Until Deadline},
   4, 'Planning',
   3, 'Execution',
   2, 'Loose ends and review',
   1, 'Launch',
     'Out of range'
)

Using SWITCH in this instance removes the need for nesting, which is bound to cause headaches (especially when working with more complex formulas than the examples here).