Besides other logical functions, Excel has the Switch function specially designed for the evaluation of a value against other values. Using the Switch function, you will be able to calculate the value in opposition to other a list of values. It eventually returns an answer equivalent to the first value. When you don’t have a matching value, you will get the default value in return.
Earlier versions of MS Excel don’t acquire this function. The Switch function was first introduced in MS Excel 2016. More or less, this function is available in VBA, where you can replace nested IF functions with the Switch function.
So, what is the main idea behind the invention of this function? Does anybody have thoughts on it? Basically, the purpose behind this function was to match more than one value resulting from the first matching value.
Do you know what the return value is? The return value is actually the outcome that corresponds with the first matching value. Talking about the syntax value, it appears as:
=SWITCH (expression, Val 1/result1, [val2/result2], …., [default])
Let’s have a look at the arguments used in the syntax in detail:
- The expression shows the value including date, number, or text that is supposed to be compared with value1…value145.
- ValueN is used to cover the whole amount of values ranging from Value1 to value145 and is used to compare with expression.
- ResultN is used to cover the whole amount of results ranging from result1 to result145. ResultN must be returned where the valueN argument matches.
- Optional value that appears by default is used when you found no match in the expression valueN. It clearly means that there is no corresponding value of the resultN argument. Also, keep in mind that the default value is the last in the row.
Functions are no more than 254 arguments, and you may pair them up as 126 values and result in expressions.
How to Use Excel Switch Function
Let’s dig out more to find the best examples that can answer how to use Excel Switch function effectively.
Example 1:
Suppose we need to return the value of a list given that is a group of acronyms. Remember the formula as it will be as:
=SWITCH(A6, “DR”, “Debtors”, “CR”, “Creditors”, “JE”, “Journal Entry”, “LB”, “Ledger book”, “unknown”).
Below you will find the results:
Here using an IF nested formula will not be feasible because it is a long formula. By using SWITCH function and IF function, you will be able to identify a range of different situations. On the other hand, this function can simply express an argument and a group of values as well as results.
You know the best thing about this function is that is no need to work repeatedly on the expressions. Moreover, if you need to exercise the IF function, the formula would be as:
=IF(B5= “DR”, “Debtors”, IF(A2= “CR”, “Creditors”, IF(B5= “JE”, “Journal Entry”, IF(B5= “LB”, “Ledger Book”, “unknown”))))
Example 2:
Joint SWITCH Function with Other Functions
Let’s suppose you are working with a worksheet full of dates. Now, you need to know whether the date is coming today, yesterday or tomorrow. Here you will have to practice the TODAY function to get an answer and using the DAYS function will let you have an answer to return the sum of days between two dates.
You may need to practice the formula given below:
=SWITCH(DAYS(TODAY(),C2),0, “Today”,1, “Yesterday”,-1, “Tomorrow”, “Out of Range”)
Example 3:
Plain Vanilla Version of SWITCH Function
Here you will find a huge amount of Student Data including “Student Names” and “Student Codes”. The purpose of applying the SWITCH function here is to settle down the values in the column “Student Type”.
Basically, all the students are being nominated in different groups such as “Student Code”. A list of students that are equivalent to Student Code 1 are grouped as “International” and a list of students that are equivalent to Student Code 2 are grouped as “Domestic”.
Furthermore, when you need to settle down the “Student Type” values, you can run the below-given formula:
=SWITCH(B2,1, “International”, 2, “Domestic”)
Would you like to slice up the formula given above? Let’s do this:
- Here B2 is used for the expression parameter gone through the SWITCH function. Remember that B2 is known as the cell reference value that contains 1.
- Next comes 1 that is gone through the function as ‘value1’ parameter.
- ‘International’ parameter is gone through as ‘result1’ parameter.
- It simply shows that when your expression is gone through 1 the resulting value should be ‘International’.
- Then comes 2, which is gone through the function as ‘value2’ parameter. Expression ‘Domestic’ is gone through as ‘result2’ parameter. It simply shows that when expression 2 appears the ultimate result should be ‘Domestic’.
In a nutshell, the first student with expression 1 will show the result “International”, whereas the second student with expression 2 will show the result “Domestic”. That’s how to use Excel SWITCH function effectively.
Example 4:
SWITCH Function with a Default Value
Let’s continue with the previous example to find out what happens when the expression is not matching with any value.
As you already know, the expression B7 is comparable with the values 1 and 2 to identify matches. When the expression is not comparable with any value, you will not find any default argument and the SWITCH function will return as #N/A error.
If you really want to get rid of this error, you may get a default value used in the function that never matches the value. In that case, you can add “Invalid Type” known as the default argument.
The ultimate formula will be as:
=SWITCH (B2, 1, “International”, 2, “Domestic”, “Invalid Type”)
Once you identify the default value for this function, you will have the default value such as “Invalid Type”. It typically appears when a precise match does not emerge. Also note that when there is no default value, the SWITCH function flings an error #N/A.
Things to Consider about SWITCH Function in Excel
- Remember that the SWITCH function can only calculate 126 value pairing.
- SWITCH function supports default arguments that return when there is no matching value left.
- An accurate match is needed to practice the SWITCH function. Never try using logical operators such as less than or greater than in the logic.
- You will find a #N/A error, when you don’t have a matching value and even the default argument is not identified.
