Until Excel 2019 the SWITCH function did not exist but since then it has provided a viable alternative to using nested IF functions, IFS or VLOOKUPs.
It evaluates one value (called the expression) against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. This is probably best understood by looking at the structure below and seeing how it could work in reality with an example.
=SWITCH(Expression, Value 1, Result 1, Value 2, Result 2,………….,Default)
There can be up to 126 groups of information to switch
Expression – the expression to be evaluated
Value 1 – the value to be compared with the expression
Result 1 – the result returned if the corresponding value matches the expression
Default – Value returned if none of the values are matched
NOTE: The value and the result can be duplicated up to 126 times for multiple switches
The following example can be looked at in 2 parts. The WEEKDAY function takes the date in A2 and changes it to a weekday, numbering Sunday as 1, Monday as 2 and so on.
The SWITCH function takes that number and changes 1 to Sunday, 2 to Monday, 3 to Tuesday and if it is none of these puts in the text Other day
NOTE: The calculation stops when it hits a match so it will run most quickly if the most likely matches are placed at the start.
Deciding whether to use SWITCH, Nested IFs, IFS or VLOOKUP will depend on the situation.
If you’ve enjoyed reading about how to use the SWITCH function on Excel, there’s some other blogs below that you might find useful: