Description
The Excel SWITCH function is a logical function that compares one value against a list of values, and returns a result value corresponding to the first match. You can define a default value that is returned in case no match is found.
Syntax
SWITCH( expression, value1, result1, [value2, result2], … , [default] )
Arguments
expression – The value that will be compared against the list of values
value1 – The first value in the list that will be compared against the expression.
result1 – The result value that will be returned if value1 matches the expression.
value2 – [Optional] The second value in the list that will be compared against the expression.
result2 – [Optional] The result value that will be returned if value2 matches the expression.
default – [Optional] The default value that is returned if no match is found
Return value
Result corresponding to the first matching value.
How to use the SWITCH function in Excel
The SWITCH function compares one value against a list of values, and returns a result corresponding to the first match it finds. Unlike the IFS function, it requires you to enter the expression only once and not repeatedly.
In the above example, we compare C6 against a list of values (keys) and return the corresponding label value for the first match found. The formula for this is:
= SWITCH(C6, 1,”Bad”, 2,”Ok”, 3,”Good”, “???”)
SWITCH only performs exact match comparison. That means the comparison of the expression with the values in the list is implicitly a equal to (=) comparison and you cannot apply logical operators like greater than (>) or smaller than (<) directly.
There is a workaround to this limitation: You can match a logical test against TRUE like this:
= SWITCH(TRUE, A1>1000,”Good”, A1>500,”Ok”, “Bad”)
In this case, however, you can simply use the IFS function instead.
AND formula examples
Related Tutorials
Related Functions
Excel IFS function
The Excel IFS function allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result
Excel IF function
The Excel IF function allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.
