Skip to content

Learn to Use the SWITCH Function

In this article, you will learn how to use the SWITCH function!

The SWITCH function comes in handy when you need a cell to evaluate to a certain value based on several different outcomes.

Here is what I mean.

Understanding the SWITCH Function

Let’s say that you have four choices.

  • A
  • B
  • C
  • D

Now let’s say that each choice corresponds to a specific color.

  • A – Red
  • B – Green
  • C – Blue
  • D – Yellow

You want your spreadsheet to show you the color if you provide the corresponding letter. Here is where the SWITCH function comes into play.

The SWITCH function will look at whatever letter you pick and return the color it corresponds with.

The SWITCH Function in Action

The SWITCH function requires at least three arguments but can accept up to just over 250. The arguments for the SWITCH function are:

  • Expression – This determines what the SWITCH function will do. In our example, it is the chosen letter.
  • Value1 – The first possible option. In our example, it is the letter A.
  • Result1 – This is the result if the first option is selected. In our example, it is the color Red.
  • Value2 – The second possible option. In our example, it is the letter B.
  • Result2 – This is the result if the second option is selected. In our example, it is the color Green.
  • Etc.

Hopefully, you can start to see how the pattern of arguments works for the SWITCH function.

To demonstrate, here is how the SWITCH function is used in an Excel spreadsheet.

The first argument in the SWITCH function references the selected letter in cell D2.

=SWITCH(D2,"A",A1,"B",A2,"C",A3,"D",A4)

The second argument is the letter A. This represents the first possible option.

=SWITCH(D2,"A",A1,"B",A2,"C",A3,"D",A4)

The third argument references cell A1 which contains the value Red.

=SWITCH(D2,"A",A1,"B",A2,"C",A3,"D",A4)

The arguments continue in the same fashion until all four outcomes are accounted for.

And that is how the SWITCH function can be used in Excel.

Set a Default Value

What happens if the SWITCH function is supplied with an outcome that is not among the list of choices? In other words, what if the letter E is chosen in our example instead of A through D?

To deal with this scenario, you can set a default value for the SWITCH function. The default value is used when no match is found.

You can set the default value by making it the last argument in the function following the last result.

=SWITCH(Expression, Value1, Result1, Value2, Result2, Default_Value)

For instance, the reference to cell A4 is the last result argument in our example. If we add one more argument, then that will be the default value used by the SWITCH function.

How cool is that?

This Post Has 2 Comments

    1. I don’t use the SWTICH function all that much myself; however, here is one example for you.

      Let’s say that you have calculated the results for several different categories, but you only want one result to display on a dashboard based on the selected category. This would be a great time to use the SWITCH function.
      You can setup the SWITCH function so that only the desired calculation is displayed based on whatever category is selected on the dashboard.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top