Skip to main content

How to Use the SWITCH Function in Excel

Joshua earned an MBA from USF and he writes mostly about software and technology.

The switch function is shown in use. This function is great for displaying data in one cell that is contingent on the type of data that appears in another cell.

The switch function is shown in use. This function is great for displaying data in one cell that is contingent on the type of data that appears in another cell.

The Purpose of the SWITCH Function

The SWITCH function compares an expression to a list of values and returns the first matching value. In the event that there is no match, a default value can be returned as an option.

Let's say you have a list of values and you want to change those values to a different value in another column. You may want to switch a phrase into an acronym or even change a date into a stated phase depending on how far away it is from the current date. Let's take a look a the syntax a little closer and follow with an example.

The SWITCH Function Syntax

When used, the SWITCH function needs to be inputted like a formula. An example of the syntax is shown below with an explanation of each function argument.

=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match)

Value to switch - The value or expression to match against. This is essentially a value that will be looked up in a group of cells.

Value to match1/Value returned1 - The first value and result if that value matches the value to switch. This is mandatory for the function to operate.

Value to match2/Value returned2 - (Optional) The second value and result if that value matches the value to switch. More values and results can be added.

No match message - (Optional) This part of the need requests that you add a message if there is no match.

Keep in mind that this function can be a rather long formula if you need to match lots of data. The SWITCH function can switch up to 126 values in a single function.

SWITCH Practical Example

Let's look at an example to determine a name that represents a certain date. Study the following function for a moment.

=SWITCH(DAYS(TODAY(),B5), 0, “Present date”, 1,”Yesterday”, -1,”Tomorrow”,”Unknown”).

Below you see dates followed by formulas and then the results of the formulas. Basically, the date 8/9/2022 is the present date as of this text. The switch function translated today's date to present, the last date to yesterday, and the next date to tomorrow.

how-to-use-the-switch-function-in-excel

The value to switch in the function is represented by a number in this example. That's where the DAYS function works in this function to translate the date into a number and serve as the value to switch.

Scroll to Continue
how-to-use-the-switch-function-in-excel

There are three values to match 0,1, and -1. Notice that each value to match is followed by a value as a result. This very useful function can be used for renaming any values in a worksheet.

how-to-use-the-switch-function-in-excel

Inserting the SWITCH Function

The SWITCH function can be inserted to assist individuals that are unfamiliar with the function's syntax. This insert tool helps with functions that have complexities. Inserting the function essentially walks you through each step required to return a result.

To use this method, a cell is first selected and the formulas tab must be clicked. Next, the "More Functions" button on the Excel ribbon needs to be selected. The logical selection from the list is then chosen followed by selecting the SWITCH function from the list.

To insert the SWITCH function from the formula tab you must first navigate to: Formulas→Logical→SWITCH. Note that if the categories listed here are forgotten, the insert function button under the formulas tab can be used to search for functions.

To insert the SWITCH function from the formula tab you must first navigate to: Formulas→Logical→SWITCH. Note that if the categories listed here are forgotten, the insert function button under the formulas tab can be used to search for functions.

After the functions arguments window appears, a cell reference can either be typed in or selected from the worksheet by clicking on the up arrow to the right of the Expression and Value1 fields. The expression is the value to be switched and the value1, value2, and so on are the values that the expression will be depending on which value the expression matches.

Functional Arguments

The above illustration shows the functional argument input window for the switch function.

The above illustration shows the functional argument input window for the switch function.

The below fields are blank, but as fields are filled in additional fields will appear to extend the function to multiple values.

how-to-use-the-switch-function-in-excel

References

Microsoft. (n.d.). SWITCH function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e.

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

To learn more about using functions in Excel I recommend The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder

Related Articles