How to use the SWITCH function in Excel 2016

The SWITCH function in Excel allows you to perform multiple checks on a value and return results based on conditions. Here are the details on how to use the SWITCH function in Excel .

If you have spent too much time creating a nested IF formula ( IFS function ), you will love using the new SWITCH function in Excel . This function saves you time in situations where you need to use the IFS function. Previously, this function was only available in VBA, but SWITCH was recently added to Excel 2016, Excel Online and Mobile, Excel for computers and Android phones. This article will guide you through how to use the SWITCH function in Excel 2016.

SWITCH function syntax

The SWITCH function compares an expression to a list of values ​​and returns the first matching value. If no match is found, it may return a default value.

The structure of the SWITCH function is as follows:

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

It has 4 arguments, one of which is optional:

  • Expression is a required argument used to compare value1… value 126.
  • ValueN is a value used to compare against the expression.
  • ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
  • Default is the value returned if no match is found in the valueN expressions. This argument does not have a corresponding resultN expression and must be the last argument in the function.

Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.

How to use the SWITCH function with other Excel functions

You can also combine the SWITCH function with other Excel functions. For example, you can nest it inside the IF function to increase overall efficiency and create more complex formulas.

Additionally, you can combine SWITCH with the following Excel functions:

  • SUM function : You can sum values ​​based on specific conditions using the SUM and SWITCH functions. For example, use the SWITCH function first to determine the values ​​to sum based on the conditions, and then use the SUM function to calculate.
  • INDEX And ​​MATCH Functions : Using these functions, you can extract data from a table based on certain conditions. For example, you can use the SWITCH function to determine target values, and then use INDEX and MATCH to extract the required data. You can also use the VLOOKUP function to get the corresponding values.

Compare the SWITCH function with the IFS function

The SWITCH function, like the IFS function, helps define a series of conditions. However, with the SWITCH function, you can define an expression and a series of values ​​and results, not a number of conditional statements. With the SWITCH function, you do not need to repeat the expressions again and again as in the IFS function.

For example, the IFS function below finds the exact states.

How to use the SWITCH function in Excel 2016

Explanation: Cell A2 contains the string 85-UT. The RIGHT function extracts the rightmost 2 characters from this string (UT). As a result, the IFS function returns the correct state (Utah). If the rightmost 2 characters are not equal to UT, TX, or OH, the IFS function returns a question mark. Instead of TRUE, you can also use 1=1 or something else that is always TRUE.

The SWITCH function below produces identical results but is much more readable.

How to use the SWITCH function in Excel 2016

Explanation: if the first argument (RIGHT(A2,2) in this example) equals UT, the SWITCH function returns Uta; if TX returns Texas; if OH returns Ohio. The last argument (the question mark in this example) is always the default value (if there is no match).

Similarly, you can see the example below with the ranking system, the SWITCH function looks more compact.

How to use the SWITCH function in Excel 2016

How to use the SWITCH function in Excel 2016

Let's see how the SWITCH function works in combination with other functions. Let's say we have a number of dates and want to quickly see if it's today, yesterday, or tomorrow. To do this, we'll use the TODAY function which returns the serial number of the current date and the DAYS function which returns the number of days between two dates.

You can see the SWITCH function works perfectly for this task.

How to use the SWITCH function in Excel 2016

With the IF function, the conversion requires some nesting and complexity. So you will be more prone to errors.

How to use the SWITCH function in Excel 2016

However, it is not always possible to use the SWITCH function in Excel. There are many examples where you cannot use the SWITCH function instead of the IFS function.

How to use the SWITCH function in Excel 2016

Explanation: since the symbols "<=" and="" "="">" are used here in this IFS function, you cannot use the SWITCH function.

The SWITCH function is really a useful function, it saves time and makes less mistakes.

Good luck!

See also:

Sign up and earn $1000 a day ⋙

Leave a Comment

6 AI Photo Editing Tools Better Than Photoshop

6 AI Photo Editing Tools Better Than Photoshop

There are many AI photo editors that make Photoshop a second choice and AI features are useful tools for editing photos without having to learn complex Photoshop.

Instructions to block websites from accessing the camera on Edge

Instructions to block websites from accessing the camera on Edge

With the option to adjust camera permissions on Microsoft Edge, users can easily change options for websites, thereby ensuring more privacy.

Bilgewater DTCL: Team composition, build

Bilgewater DTCL: Team composition, build

Bilgewater is clearly adding some interesting champions to Teamfight Tactics season 9.5.

External monitors can have a negative impact on laptop batteries.

External monitors can have a negative impact on laptop batteries.

Using a laptop with an external monitor is a great combination for productivity and getting work done. But over time, you may find that your laptop suddenly runs out of battery quickly and the battery life starts to decrease.

8 major disadvantages of foldable screen phones that you didnt expect

8 major disadvantages of foldable screen phones that you didnt expect

There’s no denying that foldable phones are pretty cool. But after using them for a while, there are a few quirks to them. Here are 5 major downsides to foldable phones that you might not expect!

Adobe is bringing AI video creation technology to Premiere Pro

Adobe is bringing AI video creation technology to Premiere Pro

Adobe has brought AI video creation technology to the masses in a new way, although it has yet to create a finished movie using the technology.

Latest Genshin Impact Codes April 2025

Latest Genshin Impact Codes April 2025

Genshin Impact 5.5 Code helps you exchange for Primordial Stones, Magic Minerals, experience and many other rewards.

How to Turn Your iPad into an Extra Mac Display

How to Turn Your iPad into an Extra Mac Display

macOS Catalina and iPadOS include support for a new feature called Sidecar, designed to let you use your iPad as a secondary display for your Mac.

Wallpaper 1280, beautiful Nokia 1280 phone wallpaper

Wallpaper 1280, beautiful Nokia 1280 phone wallpaper

This is a set of Nokia 1280 wallpapers, if you have ever texted to get 1280 wallpapers, brick phone wallpapers, then try looking at these wallpapers.

Hypersonic space plane reaches speed of 11,115 km/h

Hypersonic space plane reaches speed of 11,115 km/h

Venus Aerospace has revealed the first images of its new hypersonic aircraft called the Stargazer, which can reach a top speed of 11,115 km/h, equivalent to Mach 9.

Eating bananas for breakfast helps you reduce bloating

Eating bananas for breakfast helps you reduce bloating

Bloating can happen to anyone. The good news is that breakfast is a great time to add a few ingredients to your meal that can help reduce bloating. That ingredient is bananas.

OpenAI Announces Major Update to AI Image Generation in ChatGPT

OpenAI Announces Major Update to AI Image Generation in ChatGPT

OpenAI has just officially introduced a remarkable upgrade to the AI ​​image generation capability in ChatGPT, an important step forward instead of using a separate image generation model like the previous DALL-E.

Deepseek Releases Free Language Model v3 That Runs Well on Common Hardware Configurations

Deepseek Releases Free Language Model v3 That Runs Well on Common Hardware Configurations

Chinese AI startup DeepSeek has just officially released its latest large language model (LLM), DeepSeek-V3-0324.

Reasons to ditch Gboard and switch to Samsung Keyboard

Reasons to ditch Gboard and switch to Samsung Keyboard

Gboard has long been the go-to keyboard on Android phones. However, many people have recently switched to Samsung Keyboard and haven't looked back.

Sweet and Romantic Weekend Wishes for Lovers

Sweet and Romantic Weekend Wishes for Lovers

Send sweet and romantic weekend wishes to your lover. Although they are just words or messages, they will be meaningful spiritual gifts, helping to strengthen your relationship.