Excel SWITCH function

How to use the Excel SWITCH function

Description

The Excel SWITCH function is a logical function that compares one value against a list of values, and returns a result value corresponding to the first match. You can define a default value that is returned in case no match is found.

Syntax

SWITCH( expression, value1, result1, [value2, result2], … , [default] )

Arguments

expression – The value that will be compared against the list of values

value1 – The first value in the list that will be compared against the expression.

result1 – The result value that will be returned if value1 matches the expression.

value2 – [Optional] The second value in the list that will be compared against the expression.

result2 – [Optional] The result value that will be returned if value2 matches the expression.

default – [Optional] The default value that is returned if no match is found

Return value

Result corresponding to the first matching value.

How to use the SWITCH function in Excel

The SWITCH function compares one value against a list of values, and returns a result corresponding to the first match it finds. Unlike the IFS function, it requires you to enter the expression only once and not repeatedly. 

In the above example, we compare C6 against a list of values (keys) and return the corresponding label value for the first match found. The formula for this is:

= SWITCH(C6,  1,”Bad”,  2,”Ok”,  3,”Good”,  “???”)

SWITCH only performs exact match comparison. That means the comparison of the expression with the values in the list is implicitly a equal to (=) comparison and you cannot apply logical operators like greater than (>) or smaller than (<) directly.

There is a workaround to this limitation: You can match a logical test against TRUE like this:

= SWITCH(TRUE, A1>1000,”Good”, A1>500,”Ok”, “Bad”)

In this case, however, you can simply use the IFS function instead.

AND formula examples

Related Tutorials

Related Functions

How to use the Excel IFS function

Excel IFS function

The Excel IFS function allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result

How to use the Excel IF function

Excel IF function

The Excel IF function allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email