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.

Overview of Excel SWITCH Function

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


Download to Practice


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

Arguments Required/Optional Explanation
expression Required It is the value or expression that needs to match against.
value1 Required It is the first value.
result1 Required It is the result against the first value.
default_or_value2 Optional It is either default or you can provide a second value.
result2 Optional It is the result against the second value.

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.


Examples of Excel SWITCH Function

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.

=SWITCH(C4,1,"Astron",2,"Phoenix","?")

Using Excel SWITCH Function to Switch Corresponding Cell Values

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.

Using Excel SWITCH Function to Switch Corresponding Cell Values


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.

=SWITCH(TRUE,C4 >= 90," A",C4>= 80,"B",C4 >= 70,"C",C4 >=60," D", "Fail")

Using Excel SWITCH Function with Operator

Here, in the SWITCH function, I selected the TRUE as expression, provided C4 >= 90 as value1 and A as a result1C4>= 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.

Using Excel SWITCH Function with Operator

Related Content: How to Use TRUE Function in Excel (With 10 Examples)


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.

Using Excel SWITCH Function with DAY Function

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

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

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.

Using Excel SWITCH Function with DAY Function

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


Similar Readings:


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.

=SWITCH(MONTH(B5),1,1,2,1,3,1,4,2,5,2,6,2,7,2,8,2,9,2,10,4,11,4,12,4)

Using Excel SWITCH Function with MONTH Function

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.

Using Excel SWITCH Function with MONTH Function


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 city code with the city’s full name. To do it I’m going to use the dataset given below.

Using SWITCH & RIGHT Function

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

=SWITCH(RIGHT(B4,2),"SD","South Dakota","NY","NewYork","AL","Alabama","TX","Texas","HI","Hawaii","CA","California","ME","Maine","Not Found")

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.

Using SWITCH & RIGHT Function

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

Using SWITCH & RIGHT Function


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.

The SWITCH Function The IFS Function
The expression argument is used only once, The expression argument is repeated.
Length is less compared to IFS Length is larger
Easy to create and read As length is larger hard to create and read
Test more than one condition Test one condition

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.

Practice Excel SWITCH Function


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.

Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo