11/7/23, 11:16 AM How to use the Excel SWITCH function (In Easy Steps)
Excel Easy
#1 Excel tutorial on the net
Introduction Basics Functions Data Analysis VBA 300 Examples Ask us
SWITCH Search Entire Site
Go
Simple SWITCH | Advanced SWITCH | Combine SWITCH with
Chapter
other Functions | SWITCH or IFS?
Logical Functions
The SWITCH function in Excel looks up a specified value in a list
of values and returns the result corresponding to the first
Learn more,
match found.
Simple SWITCH it's
If easy
Let's start simple. The SWITCH function below looks up the Comparison
value in cell B3.
Operators
Or
Roll the Dice
Ifs
Contains Specific Text
Switch
If Cell is Blank
Absolute Value
Explanation: if the value is 1, the SWITCH function returns
Download
"Bad". If the value is 2, the SWITCH function returns "Good". If
the value is 3, the SWITCH function returns "Excellent". Excel File
switch.xlsx
The last argument (a dash in this example) is always the default
Next Chapter
value (if there's no match).
Cell References
Follow Excel
Easy
https://www.excel-easy.com/examples/switch.html 1/4
11/7/23, 11:16 AM How to use the Excel SWITCH function (In Easy Steps)
Become an
Excel Pro
300 Examples
Advanced SWITCH
Let's kick it up a notch! The SWITCH function below reduces the
price of green and blue tables by 50% (see orange arrows). All
other products are discounted by 10%.
Explanation: the formula shown above uses the & operator to
concatenate (join) the string in cell B3 and cell C3. If the value is
"TableGreen", the SWITCH function reduces the price by 50%. If
the value is "TableBlue", the SWITCH function also reduces the
price by 50%. The last argument (10% discount) is always the
default value (if there's no match).
Combine SWITCH with other Functions
Let's combine the SWITCH function with other Excel functions.
For example, the SWITCH function below looks up the result of
the RIGHT function.
https://www.excel-easy.com/examples/switch.html 2/4
11/7/23, 11:16 AM How to use the Excel SWITCH function (In Easy Steps)
Explanation: the RIGHT function extracts the 2 rightmost
characters from the string in cell B3. If UT, the SWITCH function
returns "Utah". If TX, the SWITCH function returns "Texas". If
OH, the SWITCH function returns Ohio. The last argument (a
question mark in this example) is always the default value (if
there's no match).
SWITCH or IFS?
The IFS function below produces the exact same result.
Conclusion: the SWITCH function is shorter and easier to read.
However, the SWITCH can only perform an exact match. When
using comparison operators like "<" and ">=", use IFS instead
of SWITCH.
https://www.excel-easy.com/examples/switch.html 3/4
11/7/23, 11:16 AM How to use the Excel SWITCH function (In Easy Steps)
Note: visit our page about the IFS function to learn more about
this powerful Excel function. IFS and SWITCH are only available
in Excel 2016 and later versions. If you don't have Excel 2016 or
later, you can nest the IF function.
8/10 Completed! Learn more about logical functions >
Go to Next Chapter: Cell References
Switch • © 2010-2023
Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting
https://www.excel-easy.com/examples/switch.html 4/4
