Alternatives to IF statements using SWITCH() in Airtable

The SWITCH() function can be a big time-saver when you’re working with conditional-heavy formulas (e.g. nested IF() statements). Learn more about using this function as an alternative to nested IF() statements.

Introduction

NOTE

When writing long formulas, it can sometimes be helpful to visually break up functions using tabbing and line breaks. Learn how to do this and more in this article.

SWITCH(expression [, pattern, result]... [, default])

Matches the expression against a series of pattern values, returning the result value corresponding to the first match. Pattern values must be unique. If no patterns match, returns the default value if specified, otherwise null…

SWITCH(4, 1, "one", 2, "two", "many") => "many"

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.

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. Using SWITCH in this instance removes the need for nesting. 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'
)