How to Use Excel SWITCH Function (5 Examples)

To switch a particular value with the value of your choice you can use the Excel SWITCH function. It is a comparison and referencing function in excel that compares and matches a referred cell to a list of values and returns the result based on the first match found.

In this article, I will show you various examples of using the Excel SWITCH function.

Basics of SWITCH Function: Summary & Syntax

Summary

The Excel SWITCH function compares or evaluates a given expression which is a value against a list of values and returns a result corresponding to the first match found. In case no match is found, the SWITCH function returns an optional default value. The SWITCH function is used instead of Nested IF functions.

Syntax

SWITCH (expression, value1, result1, [default_or_value2, result2],..)

Arguments

Return Value

The SWITCH function returns a result corresponding with the first match.

Version

The SWITCH function is available for Excel 2016 and later.

I’m using Excel Microsoft 365 to implement these examples.

How to Use Excel SWITCH Function: 5 Examples

1. Using Excel SWITCH Function to Switch Corresponding Cell Values

You can use the SWITCH function to return the value which is Project Name for the corresponding Project ID.

⏩ In cell F4, type the following formula.

Here, in the SWITCH function, I selected the C4 cell as expression, provided 1 as value1 and Astron as a result1. Then again provided 2 as value2 and Phoenix as a result2. Finally, provided ? as default.

Now, the SWITCH function will return the result by comparing the given value against provided values.

After that, press ENTER, and the SWITCH function will return corresponding provided results for the provided values.

Here, you can see the Project Name Astron is assigned for the value Project Id 1.

You can follow the same process or use the Fill Handle to AutoFill the formula for the rest of the cells.

2. Using Excel SWITCH Function with Operator

The SWITCH function also supports logical_operators. In case you want to switch values using any operators SWITCH function will help you to do it.

Here, I want to switch Marks with Grades using logical_operators.

Let me show you the process,

⏩ In cell E4, type the following formula to switch marks with grade.

Here, in the SWITCH function, I selected the TRUE as expression, provided C4 >= 90 as value1 and A as a result1,  C4>= 80 as value2, and B as result2, C4>= 70 as value3, and C as result, C4>= 60 as value4, and D as result4, finally, provided Fail as default.

Now, the SWITCH function will return the result by comparing the given value against all provided values.

Then, press ENTER, and the SWITCH function will return corresponding grades switching the marks.

You can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

3. Using Excel SWITCH Function with DAYS Function

If you want you can switch dates to the corresponding days using the SWITCH function, along with the DAYS function and the TODAY function.

Here, I’m going to use the given dataset below to explain the process.

⏩ In cell C4, type the following formula to switch dates with the day.

Here, in the SWITCH function, I selected the DAYS(TODAY(),B4) as expression, provided 0 as value1, and “Today” as a result1,

1 as value2, and “Yesterday” as a result2,

-1 as value3, and “Tomorrow” as a result3, finally, provided Unknown as default.

In the DAYS function, I used TODAY() as end_date and selected cell B4 as start_date.

Then, the SWITCH function will return the resultant days by comparing the given values.

Now, press ENTER, and the SWITCH function will return corresponding days switching the dates.

If you want you can follow the same process, or use the Fill Handle to AutoFill the formula for the rest of the cells.

Read More: How to Use VBA Switch Function

4. Using Excel SWITCH Function with MONTH Function

Let’s say you want to track the dates based on Quarter, then you can use the SWITCH function along with the MONTH function.

⏩ In cell C4, type the following formula to switch dates with day.

Here, in the SWITCH function, I selected the MONTH(B5) as an expression. Then, as value and result, I followed the provided chart.

Taken January to March (1,2,3) as value and provided 1 as result

Next April to June (4,5,6) as value and provided 2 as result. Then July to September (7,8,9) as value and provided 3 as result and October to December (10,11,12) as value and provided 4 as result.

In the MONTH function, I selected the B4 cell as serial_number.

Then, the SWITCH function will return the quarter by comparing the given dates.

Press ENTER and the SWITCH function will return the corresponding quarter switching the dates.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

5. Using SWITCH & RIGHT Function

You can use the SWITCH function and the RIGHT function to switch values of any particular character.

Here, I want to switch the abbreviation of the city code with the city’s full name. To do it I’m going to use the dataset given below.

⏩ In cell C4, type the following formula to switch dates with the day.

Here, in the SWITCH function, I selected the RIGHT(B4,2) as an expression. 

In the RIGHT function, I selected the B4 cell as text and provided 2 as num_chars to get the last 2 characters which are city code.

Then, as the value provided the city code and provided city full name as result.

After that, the SWITCH function will return the full name of the city.

Now, execute the formula by pressing ENTER, and the SWITCH function will switch the city codes with the full name of the city.

Here, you can follow the same process, or you can use the Fill Handle to AutoFill the formula for the rest of the cells.

Comparison Between SWITCH & IFS Function

If you want, you can use the nested IF or IFS function instead of the SWITCH function.

Let me show you the comparison between the SWITCH and IFs function.

Things to Remember

➤ The SWITCH function can handle up to 126 pairs of values and results.

➤ You can use another function and formula as an expression.

🔺 The SWITCH function shows the #N/A error if it is unable to match and there is no else argument or default condition.

➤ Whenever you get the #N/A error then to avoid this error you can use a string within inverted commas as the default value.

🔺 The SWITCH function will show the #NAME error if you misspell the function name.

Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.

Download Practice Workbook

Conclusion

In this article, I have shown 5 examples of the Excel SWITCH function.  Then, I also tried to cover the when and why the SWITCH function may show errors frequently. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

<< Go Back to Excel Functions | Learn Excel