SWITCH Excel Function

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is SWITCH Function In Excel?

The SWITCH Excel Function is a comparison and referencing function that compares and matches a referred cell to a group of cells and returns the result based on the first match found.

The Excel Switch function is an inbuilt function in Excel so that we can insert it from the “Function Library”, or enter it directly in the worksheet.

For example, we will replace the cell A1 value with a new value. Enter the formula =SWITCH(A1,“IND”,“INDIA”) in cell B1 and press “Enter”, as shown below.

Switch Function Intro

The output is shown above, i.e., the selected cell value, IND, is replaced with the new value INDIA.

Key Takeaways

  • The SWITCH Excel Function helps users replace a cell value with a new value with the syntax =SWITCH( target cell, value 1, result 1….) based on the result’s published value.
  • The function is introduced and available in Excel 2016 on Windows and MAC, but not in earlier versions of Excel.
  • We can take 126 pairs of values and results into the Excel SWITCH function.
  • We have the default value in the SWITCH function, which is not in the IF function.
  • The function is different from the VLOOKUP, but we can use it along with the VLOOKUP function.

SWITCH Excel formula

The syntax of the SWITCH Excel formula is,

SWITCH Function Example 1

The arguments of the SWITCH Excel formula are,

  • expression – The value or expression to match which is against. It is a mandatory argument.
  • value1, result1 – The first value and result pair. It is a mandatory argument.
  • value2, result2 – The second value and result pair. It is an optional argument.
  • default – The default value to use when no match is found.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use SWITCH Function In Excel?

We can use the SWITCH Excel Function in 2 ways, namely,

  1. Access from the Excel ribbon.
  2. Enter in the worksheet manually.

Method #1 – Access from the Excel ribbon

Choose an empty cell – select the “Formulas” tab – go to the “Function Library” group – click the “Logical” option drop-down – select the “SWITCH” function, as shown below.

Formulas - Switch function

The “Function Arguments” window appears. Enter the arguments in the “Expression” and the “Value1” fields, and click “OK” as shown below.

Formulas - Switch function - Function Arguments

Method #2 – Enter in the worksheet manually

  1. Select an empty cell for the output.
  2. Type =SWITCH( in the selected cell. [Alternatively, type =S or =SW and double-click the SWITCH function from the list of suggestions shown by Excel.]
  3. Enter the arguments as cell values or cell references.
  4. Close the brackets and press the “Enter” key.

Download Template

This article must help understand SWITCH Excel function with its formula and examples. You can download the template here to use it instantly.

You can download this Switch Function Excel Template here – Switch Function Excel Template

Example – SWITCH Formula

We will consider an example of the Switch formula and the explanation of the same.

SWITCH Function Example 1-1

In the table given above, we can note the following data,

  1. One of the projects is the name “Palm Court.”
  2. Different blocks that are available in the project.
  3. Other unit numbers are available in the project for their respective blocks.
  4. It is the “Price Range” that we are supposed to determine as per the assumption taken.
  5. Cell D1, or cell 1 in the Price Range column, has a formula inserted that is explained below.
SWITCH Function Example 1-2

#Switch Formula

The formula inserted is shown below.

SWITCH Function Example 1-3

Let us look at the SWITCH formula to obtain the value of cell 2, i.e., cell D2, in the “Price Range” column:

  • The SWITCH function is available in Excel 2016.
  • In this example, we have to take the block cell as the expression, as the assumptions of prices are based on block numbers.
  • Value1 and Result 1 – We have mentioned that if an expression matches value 1, then take result 1 or go to condition 2.
  • Value 2 and Result 2 – If condition 1 is not satisfied, then the expression comes to condition 2 to check whether it will get the match to obtain result 2. This step will continue until the expression matches the value.
  • If the expression does not match any value in the conditions, it will give a #NA error as output, as shown below in cell D13.
SWITCH Function Example 1-4

To mention some statements for the cells that are showing #NA, we can give a string within inverted commas as below:

SWITCH Function Example 1-5

If we evaluate the above problem using the IFs function, we need to have an Excel nested IFExcel Nested IFIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more for “True” or “False,” which is not a LOOKUP function. When it is “True”, it gives out one result, and for “False,” it gives other results.

How Different Is SWITCH From IFs?

A few differences between the SWITCH and the IF functions are as follows:

Important Things To Note

  • The function will return the #NA error if we do not mention any default condition, and if any condition does not get the match.
  • However, as a negative, we cannot use logical operators such as >, <, or = in the expression. It simply matches the values in its list to the expression and cannot test if a value is larger or smaller.

Frequently Asked Questions

1. How unique is the SWITCH Excel function?

The SWITCH function is available in Excel 2016. It is the most powerful function in place of the IF’s function.
We can use this function in the Excel VLOOKUP function to create an array that we can also do by using the CHOOSE function. However, the SWITCH function’s default argument makes it a better solution. It is not possible to use logical operators in the SWITCH function.

2. Why is the SWITCH function in Excel not working?

A few reasons the SWITCH Excel function may not work are as follows:
We are using the Excel version before 2016, where the function was not introduced yet.
We have not mentioned the default argument, so we got the #NA error.
We have dragged the formula to get the cell results of the other cell values without inserting the proper replacement value. Once again, we get an #NA error.

3. What are the alternate functions to use in place of the SWITCH Excel function?

Some of the alternate functions to use in place of the SWITCH Excel function are,
CHOOSE function – It can choose a value to be replaced with.
IF function – It is limited to 64 conditions.
VLOOKUP function – We can use a lookup_table to perform the switch.
VBA Switch – It can be inserted using the Developer tab.

This article is a guide to SWITCH Excel Function. Here we use Switch to replace cell values, instead of IF(), CHOOSE, VLOOKUP, examples & downloadable template. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *