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).