Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel SWITCH Function

We can use the Excel SWITCH function to compare one value against a list of values while working with excel spreadsheets. This function returns a value that corresponds to the first match, and an optional default value if no match is found. This post provides a step by step guide for how to use Excel SWITCH function.

Figure 1. Final result

Syntax of the formula

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

Parameters

Expression- refers to the value or expression to match against.

  • Val1/result1– first value and result pair
  • Val2/result2– second value and result pair. They are optional
  • Default– default value to be used if no match is found. It is also optional.

Understanding how the SWITCH function works

We use the SWITCH function to match a given value against many possibilities and return a result which corresponds to the first matching value.

We can hardcode the ‘expression’ constant, by using the cell reference. We can as well use another formula which can return a specific value that we want to match against. Note that matching values are entered in pairs, i.e. value/result, and it can handle up to 126 of such pairs. In the event there are no matches, the SWITCH function uses the default result to return.

In our example above, we have used the formula below in cell D2:

=SWITCH(C2, 1, "Excellent", 2, "Good", 3, "Poor", "??")

Note also that the SWITCH function is only available in Excel 2016, so if you are using another version, you might not be able to see this function.

The SWITCH function allows us to test several conditions without the need to nest them. This makes it easier read and create many formulas with many conditions.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc