The Excel SWITCH function was introduced in Excel 2016 (sorry not available in previous versions) and is a great alternative to nested IF functions.
It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.
Watch the Video
Understanding the SWITCH Function
The SWITCH function compares an expression (cell value) against a list of values, and performs the result for the matching value. If a match is not found, a default result can be set.
=SWITCH(expression, value1, result1, [default_or_value2, result2])
Expression is the value to be tested against the list of values. This is typically a reference to a cell value such as A2.
Value1, value2, … is the value to be compared against the expression.
Result1, Result2, … is the action to perform if Value is a match to the expression.
Default is the action performed if no values match the expression.
Here we can see the strengths of the SWITCH function.
It only refers to the expression once, which is great. While nested IFs will constantly reference the expression in each logical test it performs.
Excel SWITCH Function Example
In this example, the values in column B are compared against the sequence of values in the SWITCH function – Red, Green and Yellow. Then the resulting discount is returned.
If no matching value is found, then Not a valid membership is returned.
=SWITCH(B2,"Red",10%,"Green",14%,"Yellow",18%,"Not a valid membership")
A nested IF formula would have looked like below.
=IF(B2="Red",10%,IF(B2="Green",14%,IF(B2="Yellow",18%,"Not a valid membership")))
Cell B2 is constantly referenced in each IF test. There is no disguising the simplicity of SWITCH in comparison.
This is just 3 tests. If there were many more the difference would have been glaring.
Using the SWITCH Function with VLOOKUP
Let’s have a look at the Excel SWITCH function with the ever popular VLOOKUP.
In this example, the SWITCH function returns a different lookup table for each value in the list.
=VLOOKUP(C2,SWITCH(B2,"red",$F$2:$G$6,"Green",$F$9:$G$13,"Yellow",$I$2:$J$6,"Not a valid membership"),2,TRUE)
So here we have created a conditional lookup formula that returns a discount dependent upon the membership they have, and the price ordered.
These two examples demonstrate how useful an addition the Excel SWITCH function is to the family of logical functions. And although you can’t beat the classic IF function, SWITCH can certainly simplify the more complex ones.
Leave a Reply