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.
