SWITCH Function in Excel: Explained

In this article, you will learn what the SWITCH formula in Excel does and how to use it.

‍

What does the SWITCH formula in Excel do?

The SWITCH formula is a new function introduced in Excel which evaluates an expression against a list of cases and returns the result corresponding to the first matching case. It is similar to a lookup function that allows you to search for a specified value within a range of values and returns a corresponding result based on the first match found.

‍

The SWITCH function can be useful in a variety of situations. For example, you can use the SWITCH function to categorize data based on certain mapping rules like assigning month names to data that contains numerical inputs.

‍

Overall, the SWITCH function in Excel is a flexible and powerful tool that can help simplify complex calculations and categorizations.

‍

How to use the SWITCH function in Excel

The syntax for the SWITCH formula is as follows:

=SWITCH(expression, value1, result1, [value2, result2], …, [default_value])

where:

expression is the value or cell reference that you want to evaluate

value1, value2, etc. are the values to compare against the expression

result1, result2, etc. are the values to return if the corresponding value matches the expression

default_value is an optional value to return if none of the values match the expression

‍

Real Life use case for SWITCH function

Suppose you work for a company that sells products to customers, and you want to calculate the shipping cost for each order based on the customer's location and the shipping method they choose. You can use the SWITCH function to determine the shipping cost based on these factors.

‍

US customers: $5  

UK customers: $10 

Canada customers: $7.5

Other international customers: $15 

‍

In the above example for Customer ID 765974, The Switch formula used evaluates the value in C3 which is “US”. The formula then looks for “US” in the pairs of values provided after that and returns the shipping fee of 5. However for Customer 765978, the formula looks for “France” in the values provided but does not find any matching value, it therefore returns the default value provided in the formula i.e. 15. 

What is the difference between CHOOSE and SWITCH functions in Excel?

While CHOOSE and SWITCH functions sound very similar there are a few differences between the two that one should be aware of. Please see below for the differences:

1. For the CHOOSE function the index_number must be a positive whole number or a reference to a cell containing a whole number. The function returns the value at the specified index_number position from the list of values. However, the SWITCH formula compares the expression against each value and returns the corresponding result of the first match.
2. For the CHOOSE formula if the index_number is less than 1 or greater than the number of provided values, the function returns #VALUE! Error. In the SWITCH formula on the other hand, If no match is found, you can specify a default result using the optional last pair of value and result.
3. SWITCH function allows both exact and approximate matching, versus CHOOSE which only functions on INDEX exactly matching.

‍