Excel SWITCH Function
Summary
The SWITCH function compares one value against a list of values, and returns a result corresponding to the first match. SWITCH takes an optional default value which is used when no match is found.
Purpose
Match multiple values, return first match
Return value
Result corresponding with first match
Syntax
=SWITCH (expression, val1/result1, [val2/result2], ..., [default])
Arguments
- expression - The value or expression to match against.
- val1/result1 - The first value and result pair.
- val2/result2 - [optional] The second value and result pair.
- default - [optional] The default value to use when no match is found.
Usage notes
Use the SWITCH function to match a value of expression agains several possibilities and return a result corresponding to the first matching value. In the example shown, the formula in D5 is:
=SWITCH(C5,1,"Poor",2,"OK",3,"Good","??")
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting, making formulas with many conditions easier to read and create.
The first argument is called "expression" and can be a hardcoded constant, a cell reference, or another formula that returns a specific value you want to match against. Matching values and results are entered in value/result pairs. SWITCH can handle up to 126 matches. A optional final argument specifies a default result to return in cases where there is no match.
Note: the SWITCH function is new in Excel 2016 on Windows, and won't work in other versions of Excel. It's not yet available in Mac Excel 2016.
Notes:
- Expression can be another formula that returns a specific value.
- SWITCH can handle up to 126 value/result pairs.
- Enter a final argument to set a default result when no match is found.