# How to use SWITCH Function in Excel- 4 Examples

Using Excel’s **SWITCH** function can help you significantly improve your skills. But what exactly is the **SWITCH** function in Excel, and how does the **SWITCH** function differ from the traditional **IFS** function? Continue reading and experimenting with the examples in this article to learn more about the **SWITCH** function and how to use it in Excel to remove a long, complicated, clustered function.

## Syntax of SWITCH Function

`=SWITCH(logic1, value1, result1, …………,default)`

## Argument of SWITCH Function

*logic1:** **cell reference, number, date, or some text that is to be checked.*

*value1:** logically determined value in relation to logic1.*

*result1:** If **logic1** matches **value1**, **result1** will be returned.*

*Similarly, up to 127 logical sets can be added to the argument.*

*default: **If the function fails to match any of the values (values 1 through 126), the default value is returned.*

## Return OF SWITCH Function

The value corresponding to the first matching condition is the** SWITCH** function’s return value. The function outputs a default value if none of the situations match the desired value (which can be specified as the last argument).

**4 Examples to Use of the SWITCH Function in Excel**

### Example #1: Application of SWITCH Function in Several Cases

Assume you are provided a collection of designation acronyms in any organization and are asked to return the full form of the acronyms.

`=SWITCH(A4,"AO","Analytics Officer","DO","Data Officer","EO","Executive Officer","FO","Financial Officer","IO","Information Officer","Unknown")`

Just input the following formula in the output cell **C4** and press **Enter**

Then** AutoFill **the rest cells in column** C**

**Explanation:**

**A4 = “AO”**, Here Switch function returns**“Analyst Officer”.**- But for
**A9= “MO”,**no one value in the formula match with “MO”. So, the formula returns the default value**“Unknown”**

**Example #2: Application of SWITCH Function with other Function**

To understand the Example properly you should keep the following idea on the DAYS function

**Syntax of DAYS Function**

`=DAYS(end, start)`

**Argument of DAYS function**

*end:** Implies the end date*

*start:** implies the start date*

**Return of DAYS function**

the number of days that have passed between two dates.

Now, try the example.

Suppose you are given some random date and you want to define today, tomorrow, and yesterday.

`=SWITCH(DAYS(TODAY(),B3), 0, "Today", 1,"Yesterday", -1,"Tomorrow","Unknown")`

**Explanation:**

**TODAY**function picks the current day- The
**DAYS**function counts the difference between today and the day given in**B3**. - If the difference comes to
**Zero**, the**SWITCH**function returns**“Today”** - If the result of
**DAYS(TODAY(),B3)**will not match with 0, 1, -1, then the**SWITCH**function will return the default value**“Unknown”**

**Example #3: Combined Application of the SWITCH and the VLOOKUP Functions**

Before starting the Example, please keep a glance to the following syntax of VLOOKUP Function

**Syntax of the VLOOKUP function**

`=VLOOKUP(value, range, column_number, match_type)`

**The argument of the VLOOKUP function**

*value: **the value you want to lookup*

*range**: the range in the Excel sheet containing the lookup value*

*column_number**: The column number from which you want to return a value corresponding to the range.*

*match_type**: **TRUE** for the approximate match type, **FALSE** for the exact match type*

Now, let’s start with an example.

Suppose you are given a chart containing some students’ names and marks in English and math.

You want to create a lookup formula to return the number obtained by any student as per user input. Of course, you can utilize the combined application of the **SWITCH** and the **VLOOKUP** functions.

`=VLOOKUP(G3,B3:D7,SWITCH(G4,"English",2,"Math",3),TRUE)`

**Explanation:**

**SWITCH(G4,”English”,2,”Math”,3):**The**SWITCH**function inserts the**2**or**3**in the position of**column_number**in the syntax of the**VLOOKUP**function based on the input in cell**G4**.- Then, the
**VLOOKUP**function returns the number obtained for a particular subject.

We can simplify the above formula as follows:

`=VLOOKUP(“Daniel”,B3:D7,2,TRUE)`

**Example #4: Creating Grade Sheet **

In this example, we will create a grade sheet using the **SWITCH** formula.

Marks and grades will be distributed as below,

Just input the following formula in the output cell **D3**

`=SWITCH(TRUE,C3 >= 80,"A+",C3 >= 70,"A",C3 >= 60,"A-",C3 >= 40,"B", "Failed")`

**Explanation:**

- According to the syntax of the
**SWITCH**formula, if one condition is satisfied or**TRUE**, it terminates. - For
**Daniel,****C3=80**so the first condition is satisfied. so, the grade is**A+** - But for
**John**, no one condition is satisfied. So, the default value**“Failed”**returns.

**Things to Keep in Mind on SWITCH Function**

- In the argument, up to
**126**pairs of values and results can be inserted. - If the
**SWITCH**function is unable to match and no default value is given in the argument, the**SWITCH**function will return an**#N/A**Error. - The
**SWITCH**is added in Microsoft Excel 2019 and Office 365. So, the former version will return**#NAME?**Error, if the**SWITCH**function is used.

**IFS vs SWITCH **

Microsoft Excel 2019 and Office 365 both support the **SWITCH** and the **IFS** Function. Furthermore, both are used to handle multiple condition sets. Only exact matching is supported by the **SWITCH** function. You cannot test any logical expression (**<, >, or =**). If you want to see different values corresponding to the first condition, the **SWITCH** function has the advantage of allowing you to specify multiple values to return. The **SWITCH** function does not need to be repeated. To create an array, the **SWITCH** function in Excel can be used with the **VLOOKUP** function, which we can also do with the **CHOOSE** function.

Although the **IFS** function supports logical expressions, you can only specify one value to return if the condition is met.

## Conclusion

When working with enormous datasets and numerous criteria, SWITCH Function in Excel is especially helpful. It provides users with the ability to describe several cases in a single calculation, which makes handling conditional logic more organized and effective. It is a helpful substitute for the nested IF function and can help users save time and increase the accuracy of their work by streamlining complex calculations and making Excel formulas easier to read. These functions can be used effectively in data analysis, modeling, and reporting.