Function: SWITCH

by | Feb 5, 2019 | Excel Formulas

SWITCH

The SWITCH function evaluates the value in its first argument against the value-result pairs defined with them, and returns the matched pair if there is a match, and the default value if there isn't. The SWITCH function is a good alternative to the IF function for when you want to return a value based on the result of another expression. In this guide, we’re going to show you how to use the SWITCH function and also go over some tips and error handling methods.

Supported versions

  • Excel 2016 with Office 365 and above

Syntax

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

Arguments

expression

Expression is the value (i.e. a number, date, or text) to be compared against value1…value126.

value1…value126 

ValueN is the value to be compared against the expression.

result1…result126 

ResultN is the value to be returned when the corresponding valueN argument matches the expression. ResultN and must be provided for each corresponding valueN argument.

default

(Optional) The default value to return in case no matches are found with valueN expressions. The default argument is identified by having no corresponding resultN expressions. The default value must be the final argument in the function.


Examples

=SWITCH(F4,"S","36-38","M","39-41","L","42-45","No match")
formula initially checks the value of the cell F4, ("L"  in our case) and compares it against value arguments. When the expression and the value match, result of the value returns from the formula, "42-45".

SWITCH

Download Workbook

Tips

  • Use the IFS function if you want to utilize multiple expressions.
  • The SWITCH function only allows exact matching. Use the IFS with operators like greater than (>) or less than (<) in the logical expression.Use the IFS function if you want to utilize multiple expressions.

Issues

#N/A

If the expression doesn't match any value arguments and a default result is missing, the function returns #N/A.