Скачать или смотреть When is Using “SWITCH” Function More Efficient Than Using “IFS” in Excel

Both SWITCH and IFS are new functions released in Office 365 and Excel 2019. Although one of them can replace the other basically IFS can be used instead of SWITCH but not necessarily the opposite both functions are important, and each one is better used than the other in specific situations.

In my previous post, I elaborated, with a video illustration, on why and how the IFS function is used. In short, it is best used when we need to test multiple values in different cells or expressions. Whereas the SWITCH function is used to test a single value in one cell or expression.

So, in my previous example of bonus calculation, if the bonus formula is dependent on the employee’s performance only (with no dependency on the Regional Target Achieved), it is more convenient and efficient to use the SWITCH function rather than the IFS function.

For programmers,

IFS resembles the following structure:
IF condition1 THEN
result1
ELSEIF condition2 THEN
result2
ELSE
result3
END IF

Whereas SWITCH resembles the structure of:
SELECT CASE variable
CASE IS value1
result1
CASE IS value2
result2
CASE ELSE
result3
END SELECT

In this video I present the same bonus calculation example using SWITCH function with the employee’s performance as the only condition that bonus depends on.