Blog

The SWITCH Function in Excel

If you use the nested IF function frequently in Excel, then you might want to Switch things around.
If you use the nested IF function frequently in Excel, then you might want to Switch things around.

The Nested IF is powerful, but if you have a bunch of nested conditions, then you're going to lose track of the nesting levels.

There is an alternative function in Excel that's built for exactly this scenario.

It's called the SWITCH Function.

As the name suggests, it evaluates an expression, and depending upon the possible values of that expression, you can make it return different results.

For instance, let's look at this example that involves analyzing somes sales figures and ranking them based on their values.

We want a sales figure of 5000 to be given Rank 1, sales figure of 4000 to be given Rank 2, and so on, until we reach 1000. If the sales figure is none of these values, then we can return some text such as "Undefined rank".

You might know how to do this using the Nested IF function.

Basically, we start by comparing the value in the cell in question to the value 5000, and if it's true, we return Rank 1, otherwise we write a nested IF to compare the value in the cell to 4000, and so on. As you can see, this is a very long-winded way to write a function.

There's a better way, and it involves using the SWITCH Function.

The first argument you give to the switch function is the expression you want to evaluate. In this case, it's just the cell that contains the value we want to evaluate.

Then we just write down the value-result pairs for evaluating this expression. The first value result pair is 5000, "Rank 1", the second is 4000, "Rank 2", and so on. The advantage, as you can see, is that we did not have to write a bunch of nested IFs, making the function complicated and prone to error.

At the end, you can even provide a default result if none of the values match the expression.

Now, you might have heard of the VLOOKUP function. Whenever you have a scenario like this that involves NESTED IF conditions, it's actually a better idea to use the VLOOKUP function. Or Index-Match. Or XLOOKUP, depending on your Excel version.

The advantage of VLOOKUP or XLOOKUP or Index-Match is that you can keep your reference table separate from the expression to be evaluated. In this example, we create a reference table corresponding to the evaluation of the sales figures. We then use the VLOOKUP function to compare the value in the cell to the first column in our reference table, and if there's a match, then return the value in the second column of the reference table.

The advantage is that, tomorrow, if we decide that 4000 must be Rank 3 or Rank 4, we only need to update the reference table. We don't need to touch the formula itself.

This makes the formula much more robust when something changes.

Have you encountered a situation when you had to use the NESTED IF function? Did you use the SWITCH FUNCTION instead?

Or did you use VLOOKUP, Index-Match, or XLOOKUP?

Remember, it's easy to write a formula, whether it's a NESTED IF, the SWITCH FUNCTION, or a VLOOKUP.

But before you write your functions, think about the purpose for writing them. And choose the function that's best suited for the purpose at hand.

And that's much more flexible and adaptable in case things change in your data.

To learn more, watch this video.