Skip to Content

How to use Excel SWITCH Function (With Examples)

The Excel SWITCH function allows you to compare an expression (or a value) to a list of possible values. You can define the desired result for each value. If the expression (or a value) matches any value from the list, the function returns the corresponding result.

It has become one of the easiest alternatives to nested IF statements.

For example, you can assign grades to each employee based on their performance rating. We have the following data,

Our task is to assign the following grades to employees:

  • Excellent: If the performance rating is 5
  • Good: If the performance rating is 4
  • OK: If the performance rating is 3
  • Needs Improvement: If the performance rating is 2
  • Unsatisfactory: If the performance rating is 1

Here’s the formula,

=SWITCH(B2, 5, "Excellent", 4, "Good", 3, "OK", 2, "Needs Improvement", 1, "Unsatisfactory")

Where,

  • B2 is the cell holding the value to be compared
  • Excellent is the desired result if the value in cell B2 is equal to 5
  • Good is the desired result if the value in cell B2 is equal to 4
  • OK is the first desired if the value in cell B2 is equal to 3
  • Needs Improvement is the desired result if the value in cell B2 is equal to 2
  • Unsatisfactory is the desired result if the value in cell B2 is equal to 1

The results are displayed in the following GIF,

Syntax

The SWITCH is a simple function with the following syntax. It is the most common alternative to nested IF statements. 

=SWITCH(expression, value1, result1, [default or value2, result2], …)

Where,

  • expression needs to be replaced with the cell reference or value that needs to be compared with the given values
  • value1 is the first value that will be compared against the expression (or a value)
  • result1 is the desired output if the expression matches the first value

Important Notes:

  • Expression can be a formula that returns a specific value
  • You can input up to 126 pairs of values and results
  • The function is not case-sensitive
  • It does not support wildcards
  • Make sure to provide a final argument to be returned if no match is found

How to use the SWITCH Function in Excel

Download the example spreadsheet used to demonstrate the SWITCH function in the upcoming section. Practice is the key to success!

Download Now

Example 1: SWITCH Function Basics

Suppose that you own a small online store. Here are the orders that need to be processed today.

Our task is to add the estimated delivery time in column D. It depends on the shipping method selected by the customer.

  • Express Shipping – Delivery within 1 working day
  • Standard Shipping – Delivery within 3 working days
  • Economy Shipping – Delivery within 5 working days
  • FREE Shipping – Delivery within 10 working days

Here are the steps,

  • Select the cell E1
  • Type =SWITCH
  • Select the first option from the popup
  • Add the cell reference as D2
    (Column D includes shipping methods selected by the customers
  • Type , on your keyboard
  • Specify the first value “Express”
    (Make sure to use the double quotation marks)
  • Type ,
  • Put 1 in the place of the result1 argument
    (This is our desired output if the value in the cell D2 matches the first specified value “Express”)
  • Type ,
  • Specify the second value “Standard”
  • Type ,
  • Put 3 in the place of the result2 argument
  • Next, repeat the above steps to add the remaining shipping methods and corresponding delivery times as shown below
  • Complete the bracket for the SWITCH function using )
  • Press the Enter key

Our final formula is as follows,

=SWITCH(D2, "Express", 1, "Standard", 3, "Economy", 5, "FREE", 10)

Where,

  • D2 includes the shipping method to be compared
  • “Express” is the first value to be compared
  • 1 is the desired result if the first value matches the contents of the cell D2
  • Standard, Economy, and FREE are the next values to be compared
  • 3, 5, and 10 are desired results if any of the previous values matches the contents of the cell D2

The results are displayed in the following image,

Example 2: Combining SWITCH with Other Functions in Excel

Suppose that we wish to categorise the orders based on the day of the week. We will use two categories: Weekday and Weekend.

Refer to the following image,

To solve this problem, we will combine the TEXT and SWITCH functions in Excel.

The Excel TEXT function lets you convert numbers to text. For example, it can convert a date into month, year, or day of the week as follows:

You can convert a date into a text value or a number as shown above. It depends on the type of format you choose.

We will convert the order date from our date into the week of the day using the “dddd” format.

Here are the steps,

  • Select the cell E2
  • Type =SWITCH
  • Choose the first option from the popup
  • Type TEXT
    (As text is a built-in function in Excel, you will see the following popup)
  • Choose the first option from the popup
  • Provide the cell reference D2
    (Column D includes the order dates)
  • Type ,
  • Put “dddd”
    (This is the format to convert the date into the day of the week)
  • Complete the bracket for the TEXT function using )
  • Type ,
  • Now, type “Monday”
    (This is the first value to be compared with the results returned by the TEXT function)
  • Type ,
  • Put “Weekday Order”
    (It is the desired result if the TEXT function output matches the first value defined by us in the previous step)
  • For the rest of the values to be compared, copy and paste “Weekday Order”, “Tuesday”, “Weekday Order”, “Wednesday”, “Weekday Order”, “Thursday”, “Weekday Order”, “Friday”, “Weekday Order”, “Saturday”, “Weekend Order”, “Sunday”, “Weekend Order”
  • Type ,
  • Put “Invalid Date”
    (This is the default output, if the given date is invalid)
  • Complete the bracket for the SWITMCH function using )
  • Hit the Enter key

Our final formula is as follows,

=SWITCH(TEXT(D2, "dddd"),
     "Monday", "Weekday Order",
     "Tuesday", "Weekday Order",
     "Wednesday", "Weekday Order",
     "Thursday", "Weekday Order",
     "Friday", "Weekday Order",
     "Saturday", "Weekend Order",
     "Sunday", "Weekend Order",
     "Invalid Date"
)

Where,

  • TEXT(D2, “dddd”) is the formula to get the day of the week in the text format
  • Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday are the values to be compared with the output of the TEXT function
  • Weekday Order and Weekend Order are the desired outputs if the corresponding values match the output of the TEXT function
  • Invalid Date is the default output if the given date is invalid

The results are displayed in the following image,

Note that I am using Conditional Formatting above to highlight Weekend orders.

You can define simple as well as complex formulas in the place of the expression argument of the SWITCH function. All you need to do is make sure that it returns a single numeric or text value as the output.

Example 3: Combining TRUE with the SWITCH Function

The SWITCH function lets you match specific values. Simply put, it allows you to compare a single expression with multiple possible values.

But what if you wish to test multiple expressions against multiple possible values? Similar to what we do with the IFS function in Excel.

=IFS(B2>=90, "Diamond", B2>=60, "Gold", B2>=40, "Bronze", B2<40, "Failed")

Here, we are saying:

  • If B2>=90, return Diamond as the output
  • If B2>=60, return Gold as the output
  • If B2>=40, return Bronze as the output
  • If B2>=90, return Failed

Note that the IFS returns the value corresponding to the first condition that is evaluated as TRUE. To learn more about the IFS function, click here. 

The same thing is possible with the SWITCH function. All you need to do is use the TRUE as an expression.

Our task is to assign the grades as follows:

  • Diamond: Scored 90+ marks
  • Gold: Scored 60+ marks
  • Bronze: Scored 40+ Marks
  • Failed: Score is below 40

Refer to the following image,

Our formula will be as follows,

=SWITCH(TRUE, B2>=90, "Diamond", B2>=60, "Gold", B2>=40, "Bronze", "Failed")

Where,

  • TRUE acts as a logical expression
  • B2>=90 is the first condition that will return TRUE or FALSE as output
  • Diamond is the desired output if the first condition is TRUE
  • B2>=60 is the second condition that will return TRUE or FALSE as output
  • Gold is the desired output if the second condition is TRUE
  • B2>=40  is the second condition that will return TRUE or FALSE as output
  • Bronze is the desired output if the second condition is TRUE
  • Failed is the default output if all of the conditions are FALSE

The results are displayed in the following image,

By combining SWITCH with the TRUE function, you are simply saying: “Return the result for the first condition that evaluates to be TRUE.“

Takeaway

The SWITCH is a powerful function in Excel. It offers the easiest way to test:

  • Single Expression against Multiple Values
  • Multiple Expressions against Single Values for Each

The function is a cleaner alternative to Excel functions such as IF, IFS, CHOOSE, etc. It can be combined with the other functions to create complex formulas. 

I hope this article taught you all the bells and whistles of the SWITCH function. Please comment below if you are stuck or encounter any particular error while using the Excel SWITCH function. I will answer your questions as soon as possible.

Additional Resources:

  • Learn All Excel Logical Functions (With Examples)
  • IFS Function in Excel
  • Choose Function in Excel
  • Nested IF Statement
  • TRUE Function in Excel

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.