Excel SWITCH Function (Table of Contents)
SWITCH Function in Excel
The SWITCH function in Excel is used to match the first value or expression from the list of available options and returns to the corresponding value.
In MS Excel 2016, SWITCH has been added and is therefore not available in earlier versions. This function in excel had previously been available in VBA, however. Instead of nested IF functions, the Switch function in excel can be used.
When we try to match multiple cases we use IF function with many IF function to satisfy many conditions to meet our criteria. The SWITCH function in excel works similarly but without nested formulas. SWITCH uses only one function to see all the criteria but the eventual result will be the same as the IF condition.
Syntax of SWITCH Function in Excel
The Syntax of SWITCH function includes 4 parameters. Out of 4 parameters of SWITCH functions , 3 are mandatory and the fourth one is optional based on our requirement.
Expression: This is simply the value or cell we are trying to test the logic.
Value1: This the test value or logical value comparing to the Expression. We can give 126 logical tests.
4.8 (1,635 ratings)
Result1: If the first logical test is true what should be the result we want.
Default or Value 2: Here we can see two things. One is Default if the first logical test is FALSE what the default value we want. Second one is Value2, this is the second logical test for the Expression.
Result 2: This is the result of the second logical test we testing.
How to Use SWITCH Function in Excel?
Using SWITCH Function in Excel is very easy. Let’s understand the Function in Excel by some examples given below.
SWITCH Function in Excel – Example #1
As I said at the beginning SWITCH is an alternative to IF condition in excel. Now we will see how simple IF condition works and try to compare the SWITCH with IF condition in excel.
I have a few fruit names here. I have 4 fruit names from cell A2 to A5 in excel.
Each fruit has its own status here, the status is based on how they impact human body temperature. Let me apply IF condition to test the results. The formula for this method is :
=IF (A2=”Apple”, “Medium”, IF(A2=”orange”, “Cold”, IF(A2=”Sapota”, “Heat”, IF(A2=”Watermelon”, “Cold”, “???”))))
IF function tests if the value in the cell A2 is equal to Apple then the result should be Medium. If the result is false then again one more IF condition will test if the value in the cell A2 is equal to Orange the result should be Cold, If the result is again false then one more IF condition will tests if the value in the cell A2 is equal to Sapota then the value will be Heat and so on. Finally, if none of the condition is TRUE then it will return the result as ???
Like this IF condition tests multiple criteria’s and gives the result based on the logical tests. In the above example, we have applied totally 4 different IF conditions to get the result this why we call it as nested IF condition.
However, SWITCH function can replace those many IF conditions and can give the result in one single formula.
Formula for SWITCH Fuction is:
=SWITCH (A2, “Apple”, “Medium”, “Orange”, “Cold”, “Sapota”, “Heat”, “Watermelon”, “Cold”, “???”)
Now, look at the above results from SWITCH function it is exactly the same as the IF condition. Let me break down the formula into pieces for better understanding.
=SWITCH(A2,”Apple”, “Medium”, : This is the first argument of the function. If the cell A2 value is equal to Apple then will get the result as Medium.
“Orange”,”Cold”, : Unlike IF condition we need not to again put one more SWITCH condition here, instead, we can go on in the formula. We need to again mention the expression here (A2 cell) rather simply enter the condition straight away. The condition is If the cell A2 value is equal to Orange then the result should be Cold.
“Sapota”,”Heat”,: Similarly if the cell A2 value is equal to Sapota then the result should be Heat.
“Watermelon”,”Cold”, : If the cell A2 value is equal to Watermelon then the result should be Cold.
“???”) : This is the final part of the formula. If none of the above 4 conditions are satisfied then the result will be ???
Like this SWITCH function can be used as an alternative to nested IF conditions. But SWITCH function has its own limitation too, we will see that limitation in the below section.
SWITCH Function in Excel – Example #2
The problem with the SWITCH function is limited to exact matching only. It cannot use any kind of operators in the formulas. Some of the common operators are >=(greater than or equal to), <=(less than or equal to) etc..
Take a look at the example now. Typical examination example, I have scores and criteria.
I will apply IF formula to determine the results.
For this apply the formula given below:
=IF(A2>=85, “Distinction”, IF(A2>=75, “First Class”, IF(A2>=60, “Second Class”, IF(A2>=35, “Pass”, “Fail”))))
But SWITCH function cannot accept operator symbols to determine the result and still we have to rely on nested IF conditions to get the results.
Things to Remember
- SWITCH Function in Excel works only for exact matching.
- In case of operator, symbol to determine the logic we have to rely on IF conditions.
- SWITCH Function in Excel can handle 126 logical tests and results but IF is limited to 64 conditions.
- It is recommended to use SWITCH Function in case of exact match scenarios or else use nested IF conditions.
This has been a guide to SWITCH Function in excel. Here we discuss how to use SWITCH Function in excel and it’s limitations along with practical examples and downloadable excel template. You can also go through our other suggested articles–