Excel SWITCH Function – Nested IF Function Alternative

0 Flares Filament.io 0 Flares ×

The 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 and weaknesses 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.

However as a negative, you cannot use logical operators such as >, < or = in the expression. It simply matches the values in its list to an expression, and cannot test if a value is larger or smaller.

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

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 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)

SWITCH with the VLOOKUP function

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 SWITCH 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

Leave a Reply

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