Excel SWITCH Function

Generic Formula

=SWITCH(expression,val1/result1,[val2/result2],...,[default])

Explanation

The Excel SWITCH function is used to compare one value against a number of other values and then returns the first matching value. If the match is not found, SWITCH takes an optional default value.

Purpose of the SWITCH function

To match multiple values and return the first matched value.

Return value

The result that is bound with the first matched value.

Arguments

  • expression – The value to match against
  • val1/result1 – First pair of the value and result
  • val2/result2 – a Second pair of the value and result [Optional]
  • default – Default value that is used when there is no match found. [Optional]

Usage Notes

SWITCH function can be used to match a value against multiple possible values and return a result that is similar to the first possible matching value.

Example

In the example below, the formula we entered in D6 is:

=SWITCH(C6,4,"Excellent",3,"Good",2,"Fair",1,"Poor","??")

Figure 1. Example of the Excel SWITCH Function to Return the Matched Values

We have defined C6 as a rating value -1, 2, 3, etc., and it will return the corresponding result of these values, such as Excellent for 4 and Poor for 1.

The SWITCH function let you test multiple conditions without nesting. The formula normally consists of many conditions that make it easier to handle.

Notes

  • The SWITCH function is not available in versions up to Excel 2013. It’s available only in Excel 2016.
  • 126 values or a pair of results can be handled by the SWITCH function.
  • The expression can not only be a value but a formula too.
  • In case of no match found, a final argument is entered to return a default result.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar