We can use the Excel SWITCH function to compare one value against a list of values while working with excel spreadsheets. This function returns a value that corresponds to the first match, and an optional default value if no match is found. This post provides a step by step guide for how to use Excel SWITCH function.
Figure 1. Final result
Syntax of the formula
=SWITCH(expression, val1/result1, [val2/result2]…., [default])
Expression- refers to the value or expression to match against.
- Val1/result1– first value and result pair
- Val2/result2– second value and result pair. They are optional
- Default– default value to be used if no match is found. It is also optional.
Understanding how the SWITCH function works
We use the SWITCH function to match a given value against many possibilities and return a result which corresponds to the first matching value.
We can hardcode the ‘expression’ constant, by using the cell reference. We can as well use another formula which can return a specific value that we want to match against. Note that matching values are entered in pairs, i.e. value/result, and it can handle up to 126 of such pairs. In the event there are no matches, the SWITCH function uses the default result to return.
In our example above, we have used the formula below in cell D2:
=SWITCH(C2, 1, "Excellent", 2, "Good", 3, "Poor", "??")
Note also that the SWITCH function is only available in Excel 2016, so if you are using another version, you might not be able to see this function.
The SWITCH function allows us to test several conditions without the need to nest them. This makes it easier read and create many formulas with many conditions.