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.