Exceljet

Quick, clean, and to the point

Excel SWITCH Function

Excel SWITCH function
Summary 

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match. SWITCH can return an optional default value 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.
Version 
Usage notes 

The SWITCH function compares one value against a list of values, and returns a result that corresponds to the first match. You can use the SWITCH function when you want to perform an exact match with several possible results, and return a default value when no match is found. 

The first argument is called "expression" and can be a hard-coded constant, a cell reference, or another formula that returns a specific value you want to match against. Matching values and corresponding results are entered in pairs. SWITCH can handle up to 126 pairs, after which the last argument is an (optional) default value to return in cases where 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 workaround 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, you may want to use the IFS function instead.

SWITCH versus IFS

Like the IFS function, the SWITCH function allows you to test more than one condition without nesting multiple IF statements, making formulas with many conditions easier to read and write. 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

  1. Expression can be another formula that returns a specific value.
  2. SWITCH can handle up to 126 value/result pairs.
  3. Enter a final argument to set a default result when no match is found.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.