This article describes the formula syntax and usage of SWITCH function in Excel/ Spreadsheet
Before the start, do have a look on “What’s new in Excel 2019 for Windows“
Table of Contents
Description of SWITCH Function
The SWITCH function in Excel 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.
Note: This function is new in Excel 2016 via Office 365.
The syntax of SWITCH Function in Excel
=SWITCH (expression, val1/result1, [val2/result2], …, [default])
| Argument | Description |
| expression (required) | The value or expression to match against |
| val1/result1 (required) | 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 |
Remarks
Match multiple values, return first match
Return value
Result corresponding with first match
Instructions to use SWITCH Function
Use the SWITCH function to match the value of an expression against several possibilities and return a result corresponding to the first matching value.
Example
In the example shown, the formula in E5 is:
=SWITCH(D5,3,"Poor",2,"OK",1,"Best","????")
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. An optional final argument specifies a default result to return in cases where there is no match.
SWITCH versus IFS
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. One advantage of SWITCH over IFS is that the expression appears just once in the function and does not need to be repeated. However, SWITCH is limited to exact matching. It’s not possible to use operators like greater than (>) or less than (<) in the logic used to determine a match in cases where there likely will not be an exact match, for example when assigning grades with a formula.
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.
Read Also: Useful Excel Keyboard Shortcuts and Function Keys for Windows
