Introduction
The SWITCH function in Excel compares one value with multiple options and returns the result associated with the matching value. It is useful for converting department codes, employee grades, order statuses, month numbers, and other fixed values into meaningful results.
In this tutorial, you will learn how to use the SWITCH function through practical business examples.
What Is the SWITCH Function in Excel?
The SWITCH function checks a given expression against multiple specified values. When it finds a match, it returns the corresponding result.
If none of the values match, you can provide a default result.
SWITCH Function Syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Arguments:
- expression: The value you want to check.
- value1: The first value to compare.
- result1: The result returned when the first value matches.
- value2, result2: Additional values and their corresponding results.
- default: Optional result returned when no value matches.
Practical Examples of SWITCH Function in Excel
Question 1: Convert Department Codes into Department Names
Problem Statement: A company stores department codes instead of full department names. Use SWITCH to display the department name.
Data:
| Employee | Department Code |
|---|---|
| Rahul | 101 |
| Priya | 102 |
| Amit | 103 |
| Neha | 104 |
Excel Formula:
=SWITCH(B2,101,"HR",102,"Sales",103,"IT",104,"Finance","Unknown")
Expected Result:
| Employee | Department Code | Department Name |
|---|---|---|
| Rahul | 101 | HR |
| Priya | 102 | Sales |
| Amit | 103 | IT |
| Neha | 104 | Finance |
Explanation: SWITCH checks the department code and returns the matching department name.
Concept Covered: Matching numeric codes with text results.
Question 2: Convert Employee Grades into Performance Labels
Problem Statement: A company uses grades A, B, C, and D to represent employee performance. Display the corresponding performance label.
Data:
| Employee | Grade |
|---|---|
| Rahul | A |
| Priya | B |
| Amit | C |
| Neha | D |
Excel Formula:
=SWITCH(B2,"A","Excellent","B","Good","C","Average","D","Needs Improvement","Not Assigned")
Expected Result:
| Employee | Grade | Performance |
|---|---|---|
| Rahul | A | Excellent |
| Priya | B | Good |
| Amit | C | Average |
| Neha | D | Needs Improvement |
Explanation: SWITCH compares the employee’s grade with the specified grades and returns the matching performance label.
Concept Covered: Matching text values.
Question 3: Display Order Status
Problem Statement: An online store stores order statuses as numeric codes. Display the actual order status.
Data:
| Order ID | Status Code |
|---|---|
| ORD101 | 1 |
| ORD102 | 2 |
| ORD103 | 3 |
| ORD104 | 4 |
| ORD105 | 5 |
Excel Formula:
=SWITCH(B2,1,"Pending",2,"Processing",3,"Shipped",4,"Delivered","Cancelled")
Expected Result:
| Order ID | Status Code | Order Status |
|---|---|---|
| ORD101 | 1 | Pending |
| ORD102 | 2 | Processing |
| ORD103 | 3 | Shipped |
| ORD104 | 4 | Delivered |
| ORD105 | 5 | Cancelled |
Explanation: SWITCH checks the status code and displays its corresponding order status.
Concept Covered: Matching numeric values with text results.
Question 4: Convert Month Numbers into Month Names
Problem Statement: A company maintains its monthly sales report using month numbers. Convert all month numbers into their corresponding month names using SWITCH.
Complete Data:
| Month Number | Monthly Sales |
|---|---|
| 1 | 45000 |
| 2 | 52000 |
| 3 | 48000 |
| 4 | 61000 |
| 5 | 57000 |
| 6 | 68000 |
| 7 | 72000 |
| 8 | 65000 |
| 9 | 75000 |
| 10 | 82000 |
| 11 | 79000 |
| 12 | 95000 |
Excel Formula:
=SWITCH(A2,1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December","Invalid Month")
Expected Result:
| Month Number | Monthly Sales | Month Name |
|---|---|---|
| 1 | 45000 | January |
| 2 | 52000 | February |
| 3 | 48000 | March |
| 4 | 61000 | April |
| 5 | 57000 | May |
| 6 | 68000 | June |
| 7 | 72000 | July |
| 8 | 65000 | August |
| 9 | 75000 | September |
| 10 | 82000 | October |
| 11 | 79000 | November |
| 12 | 95000 | December |
Explanation: The formula checks the month number in column A and returns the corresponding month name. Enter the formula in C2 and copy it down to C13 to get all 12 month names.
Concept Covered: Matching multiple numeric values and returning corresponding text results.
Question 5: Assign Delivery Charges Based on Delivery Type
Problem Statement: An online business charges different delivery fees depending on the selected delivery type. Use SWITCH to calculate the delivery charge.
Data:
| Order ID | Delivery Type |
|---|---|
| ORD201 | Standard |
| ORD202 | Express |
| ORD203 | Same Day |
| ORD204 | Pickup |
| ORD205 | Standard |
Excel Formula:
=SWITCH(B2,"Standard",50,"Express",100,"Same Day",150,"Pickup",0,"Not Available")
Expected Result:
| Order ID | Delivery Type | Delivery Charge |
|---|---|---|
| ORD201 | Standard | 50 |
| ORD202 | Express | 100 |
| ORD203 | Same Day | 150 |
| ORD204 | Pickup | 0 |
| ORD205 | Standard | 50 |
Explanation: SWITCH checks the delivery type and returns the corresponding delivery charge.
Concept Covered: Returning numeric results based on text values.
Important Things to Remember
- SWITCH compares one expression with multiple specific values.
- It returns the result associated with the first matching value.
- The default argument is optional but useful when no value matches.
- Text values must be written inside double quotation marks.
- SWITCH checks exact matches, not number ranges.
- If no value matches and no default is provided, Excel returns
#N/A. - SWITCH is available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365.
Student Practice Tasks
Try these questions using the SWITCH function.
- Convert employee shift codes 1, 2, and 3 into Morning, Evening, and Night.
- Convert product category codes 10, 20, and 30 into Electronics, Clothing, and Furniture.
- Display payment descriptions for Cash, Card, UPI, and Bank Transfer.
- Convert customer types Regular, Premium, and VIP into their respective labels.
- Convert numeric weekday codes from 1 to 7 into their corresponding weekday names.
Final Student Challenge: Build an Employee Shift Report
A company maintains employee shift codes. Use SWITCH to display the correct shift name.
Given Data:
| Employee | Shift Code |
|---|---|
| Rahul | 1 |
| Priya | 2 |
| Amit | 3 |
| Neha | 2 |
| Karan | 1 |
Your Task:
- Use SWITCH to convert each shift code into a shift name.
- Code 1 = Morning Shift
- Code 2 = Evening Shift
- Code 3 = Night Shift
- Display “Unknown Shift” for any other code.
Expected Result:
| Employee | Shift Code | Shift Name |
|---|---|---|
| Rahul | 1 | Morning Shift |
| Priya | 2 | Evening Shift |
| Amit | 3 | Night Shift |
| Neha | 2 | Evening Shift |
| Karan | 1 | Morning Shift |
Key Takeaways
- SWITCH compares one value with multiple specific options.
- It returns the result associated with a matching value.
- It can return text or numeric results.
- The default argument helps handle unmatched values.
- It is useful for employee reports, order tracking, monthly sales reports, and business data.
Frequently Asked Questions (FAQs)
1. What is the SWITCH function in Excel?
The SWITCH function compares a value with multiple specified values and returns the result associated with the matching value.
2. What is the syntax of the SWITCH function?
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
3. Can SWITCH return text in Excel?
Yes. SWITCH can return text such as HR, Sales, Pending, or Delivered.
4. Can SWITCH return numbers?
Yes. SWITCH can return numeric results such as delivery charges, commission amounts, or discount values.
5. What happens when no value matches?
If no value matches and you have not provided a default result, Excel returns #N/A.
6. Is the default argument compulsory?
No. The default argument is optional. However, including it helps display a meaningful result when no match is found.
7. Can SWITCH check number ranges?
SWITCH is designed to compare exact values, not ranges. For example, it cannot directly check whether marks are greater than 80.
8. Which Excel versions support SWITCH?
SWITCH is available in Excel 2019, Excel 2021, Excel 2024, and Microsoft 365.

