• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel SWITCH Function – Nested IF Function Alternative

Excel SWITCH Function – Nested IF Function Alternative

The Excel SWITCH function was introduced in Excel 2016 (sorry not available in previous versions) and is a great alternative to nested IF functions.

It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.

Watch the Video

Understanding the SWITCH Function

The SWITCH function compares an expression (cell value) against a list of values, and performs the result for the matching value. If a match is not found, a default result can be set.

=SWITCH(expression, value1, result1, [default_or_value2, result2])

Expression is the value to be tested against the list of values. This is typically a reference to a cell value such as A2.

Value1, value2, … is the value to be compared against the expression.

Result1, Result2, … is the action to perform if Value is a match to the expression.

Default is the action performed if no values match the expression.

Here we can see the strengths of the SWITCH function.

It only refers to the expression once, which is great. While nested IFs will constantly reference the expression in each logical test it performs.

Excel SWITCH Function Example

In this example, the values in column B are compared against the sequence of values in the SWITCH function – Red, Green and Yellow. Then the resulting discount is returned.

If no matching value is found, then Not a valid membership is returned.

=SWITCH(B2,"Red",10%,"Green",14%,"Yellow",18%,"Not a valid membership")
SWITCH function example instead of nested IF

A nested IF formula would have looked like below.

=IF(B2="Red",10%,IF(B2="Green",14%,IF(B2="Yellow",18%,"Not a valid membership")))

Cell B2 is constantly referenced in each IF test. There is no disguising the simplicity of SWITCH in comparison.

This is just 3 tests. If there were many more the difference would have been glaring.

Using the SWITCH Function with VLOOKUP

Let’s have a look at the Excel SWITCH function with the ever popular VLOOKUP.

In this example, the SWITCH function returns a different lookup table for each value in the list.

=VLOOKUP(C2,SWITCH(B2,"red",$F$2:$G$6,"Green",$F$9:$G$13,"Yellow",$I$2:$J$6,"Not a valid membership"),2,TRUE)
Excel SWITCH function with VLOOKUP

So here we have created a conditional lookup formula that returns a discount dependent upon the membership they have, and the price ordered.

These two examples demonstrate how useful an addition the Excel SWITCH function is to the family of logical functions. And although you can’t beat the classic IF function, SWITCH can certainly simplify the more complex ones.

More Excel Tutorials

  • 4 alternatives to nested IF functions
  • 5 reasons why your Excel formula is not calculating
  • 4 MOD function examples – The hidden brilliance of this function
  • Excel formula to return the least frequent value in a list

Related Posts:

  • SEQUENCE Function in Excel
    SEQUENCE Function in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Entering Fractions in Excel
    Entering Fractions in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·