The SWITCH function is a highly useful function which can be used for various applications. This is newly introduced to Excel and it is only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions). Therefore when opening an SWITCH function containing excel workbook in an earlier version of excel, the SWITCH function containing cells would become unusable.

The SWITCH function evaluates an expression with one or more pre-specified values and then returns the corresponding result to the first matching value. If there is no match, then a default value may returned. This would happen if only a default value is entered in the syntax.

The syntax used is explained below:

SWITCH(expression, value1, result1, [default or value2, result2],[default or value3, result3]….)

expression:                        This is the value to be compared with values, i.e. value1, value2….. (Required)

value1:                                 This is to be compared against the expression.

result1:                                result1 will be returned if the corresponding value (i.e. value1) matches with the expression.

default:                                The default is set to return, if no matches found form the values specified. The is on need of result argument required for this.

Note: Up to 126 pairs of value and result arguments can be added to this function, following the same argument style.

Example #1:

Consider the following data set, the SWITC function has been used to evaluate feedback scores.

 The used syntax is explained below.

=SWITCH(B2,1,"Very Bad",2,"Bad",3,"Average",4,"Good",5,"Very Good")

B2 – the expression

1,"Very Bad" – if B2 is 1, then it will return “Very Bad”

2,"Bad" – if B2 is 2, then it will return “Bad”

3,"Average" – if B2 is 3, then it will return “Average”

4,"Good" – if B2 is 4, then it will return “Good”

5,"Very Good" – if B2 is 5, then it will return “Very Good”

Note: The default argument has not been used in the above example.

Example #2:

See the following example, where the ‘Default’ argument has been used.

The default argument is set as ‘weekday’, if no matches found form the values specified (i.e. other than 6 and 7) then it will return ‘weekday’.