Excel’s SWITCH is a logical function that is primarily used for data manipulation. It evaluates an expression against a list of values, and returns a result corresponding to the first matching value.
Its popularity comes from its relative simplicity compared to other Excel functions that perform similar tasks, meaning it’s easier to read the formula and debug any issues.
In this guide, I’ll explain how to use the function with a real-world example, assess its benefits over other functions, and cover some of its limitations.
Microsoft added the SWITCH function to Excel in 2016, so it’s unavailable in earlier versions. If you attempt to use SWITCH in an incompatible version, Excel will returnthe #NAME? error.
The Syntax for SWITCH
Before I show you a real-life example of SWITCH in action, let’s look at the syntax:
where
While there are only twov-rpairings shown here in the syntax (v1-r1andv2-r2), you can have up to 126 pairings overall. Given that SWITCH returns a result corresponding to thefirstmatching value, it’s important to carefully consider the order of yourv-rpairings.
If you do not include the optionald, and none of the values (v#) matches the expression (e), Excel will returnthe #N/A error.
SWITCH in Action
Let me show you the SWITCH function in a real-world scenario. In this table, I have a list of students and their grades, and I need to work out their next steps based on those grades.
Since there are three different grade possibilities (A, B, and C), I need to incorporate all of these into the SWITCH formula. So, in cell C2, I will type
If any of the values or results in your SWITCH formula are not numerical, you need to surround them with quotes.
Although this looks complicated at first, when broken down, it’s actually quite logical:
After pressing Enter, because my data is in aformatted Excel table, the rest of column C will populate automatically.
Notice how cell C8 contains “GRADE REQUIRED,” because the expression in cell B8 did not match any of the values in my SWITCH formula.
If I wanted to change the outputs in column C, I would head back to cell C2, amend the formula in the formula bar, and press Enter. This change would then apply automatically to the other cells in column C.
You may be wondering why you would use SWITCH over some of Excel’s other functions that perform similar actions, such asIF,IFS, andXLOOKUP. Here are some reasons:
Avoid Repeating the Expression
To create the same results in the table above using IF or IFS, I would have to repeat the expression each time:
With the SWITCH function, however, I only have to state the expression once at the start of the formula:
As a result, the SWITCH function is easier to read, less prone to typing errors, and easier to review if there’s an issue.
Keep Everything in One Place
Much like SWITCH, the XLOOKUP function compares an expression to a list of values, and returns a corresponding value. However, with XLOOKUP, the list of values is in a separate table, whereas SWITCH incorporates them all into one formula. This means that you don’t have any floating data, and so, your Excel spreadsheet stays tidy.
One Set of Parentheses
If I chose to use nested IF functions to achieve the same outcomes in the table above, I would have had to use a new set of parentheses for each IF argument:
As a result, the formula ends confusingly with three closing parentheses, and debugging any issues with the syntax would be more challenging. On the other hand, when used at its most basic level without any other additional functions, SWITCH requires only one pair of parentheses.
SWITCH Drawbacks
While SWITCH has many benefits, there are a few limitations to bear in mind before you get working on your Excel spreadsheet:
SWITCH is just one of the many differentways to use data in an Excel table. You might also consider usingINDEX to find values,MATCH to find a value’s position, orINDEX and MATCH togetherto create a two-way lookup.
