SWITCH Excel Function

Publication Date :

Blog Author :

Edited by :

Download FREE SWITCH Function Excel Template and Follow Along!
SWITCH Function Excel Template.xlsx

Table Of Contents

arrow

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.

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.
  3. Enter the arguments as cell values or cell references.
  4. Close the brackets and press the “Enter” key.

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 IF 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:

  • The SWITCH function does not allow us to use logical operators in excel like greater than (>) / less than (<) to match the cases where there will not be an exact match.
  • The expression will appear only once in the Excel SWITCH function, but in the IF’s function, the expression needs to be repeated.
  • The SWITCH function’s length is less than the IF’s function, making it easier to read and create as it allows us to test more than one condition.
  • It is more like a CHOOSE function in Excel. It has an expression that is a lookup value. We would match it to value 1 and value 2 and get the result value. Unlike the IF function, we have the default value in the SWITCH function.

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 (FAQs)

1

1. How unique is the SWITCH Excel function?

Arrow down filled
2

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

Arrow down filled
3

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

Arrow down filled