SWITCH Function

=SWITCH(expression,value1,result1,default)

Simple Example

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.

SWITCH Function Example 2

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.

SWITCH Function Example 2

Our expression is in column B – the area the employee works in.

value1 is NORTH and result1 is the table array G4:H7

value2 is SOUTH and result2 is the table array J4:K7

the default value is the table array G11:H14 – if the area is not north or south then it’s east or west which have the same sales targets.

Final formula is

=IF(D2>VLOOKUP(C2,SWITCH(B2,$G$3,$G$4:$H$7,$J$3,$J$4:$K$7,$G$11:$H$14),2,FALSE),”Yes”,”No”)

Other solutions to this example would include using the CHOOSE function, but the SWITCH’s default argument makes it a better solution.

Posted by Chester Tugwell