Purpose
Return value
Syntax
=SWITCH(expression,val1/result1,[val2/result2],...,[default])- 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.
How to use
The SWITCH function compares one value against a list of values and returns a result that corresponds to the first match found. You can use the SWITCH function when you want to perform a "self-contained" exact match lookup with several possible results. When no match is found, SWITCH can return an optional default value.
The first argument in SWITCH is called "expression" and can be a hard-coded constant, a cell reference, or a formula that returns a specific value to match against. Matching values and corresponding results are entered in pairs. SWITCH can handle up to 126 pairs of values and results. The last argument, default, is an optional value to return when there is no match.
In the example shown, the formula in D5 is:
=SWITCH(C5,1,"Poor",2,"OK",3,"Good","?")
SWITCH only performs an exact match, so you can't include logical operators like greater than (>) or less than (<) in the logic used to determine a match. You can work around this limitation by constructing a formula to match against TRUE like this:
=SWITCH(TRUE,A1>=1000,"Gold",A1>=500,"Silver","Bronze")
However, in a case like this, the IFS function would likely be more straightforward.
SWITCH versus IFS
Like the IFS function, the SWITCH function allows you to test more than one condition without nesting multiple IF statements in a single self-contained formula. SWITCH therefore makes it easier to write (and read) a formula with many conditions. 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 is not possible to use operators like greater than (>) or less than (<) with the standard syntax. In contrast, the IFS function actually requires expressions for each condition, so you can use logical operators as needed.
Note: The SWITCH function and IFS function are both new in Excel 2019 and Excel 365.
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.
Related functions
IF Function
The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...
IFS Function
The Excel IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas....
CHOOSE Function
The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.
VLOOKUP Function
The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number. VLOOKUP supports approximate and exact matching...
MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...
