To help explain how SWITCH works, here is a simple example.
We need the SWITCH function to return the correct discount percentage for each item of fruit in the list D2:G9. The discounts are listed in the first table A2:B6.
The fruit value in column D is our expression – this is the value that will SWITCH or change as the discount formula is copied down column F.
The expression result is compared against the value arguments, in the order they are listed: value1, value2, value3 – you can specify up to 126 values. The first value that matches the expression returns its corresponding result – so value5 would return result5. In our example value1 is “Apple”, result1 is 5%, value2 is “Pear”, result2 is 8% and so on.
If the expression doesn’t match any of the values then SWITCH returns the #N/A! error. To prevent the #N/A! error you can specify a default value. This is useful in our example as all fruits other than apples, pears and bananas get a 2% discount, so our default value is in B6. The default value is always stated as the last argument.
Other solutions to this example would include using Nested IF, IFS or VLOOKUP.
SWITCH with VLOOKUP Example
In the next example we need to calculate whether each employee has met their sales target. The sales targets are set by area and are different for each quarter. The quickest way of looking up the correct sales target would be to use a VLOOKUP. However, there are three different tables to look across. This is where we can use SWITCH to specify the correct table array.
Final formula is
Other solutions to this example would include using the CHOOSE function, but the SWITCH’s default argument makes it a better solution.