SWITCH Function in Excel Tutorial

Excel Tutorial

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:

EmployeeDepartment Code
Rahul101
Priya102
Amit103
Neha104

Excel Formula:

=SWITCH(B2,101,"HR",102,"Sales",103,"IT",104,"Finance","Unknown")

Expected Result:

EmployeeDepartment CodeDepartment Name
Rahul101HR
Priya102Sales
Amit103IT
Neha104Finance

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:

EmployeeGrade
RahulA
PriyaB
AmitC
NehaD

Excel Formula:

=SWITCH(B2,"A","Excellent","B","Good","C","Average","D","Needs Improvement","Not Assigned")

Expected Result:

EmployeeGradePerformance
RahulAExcellent
PriyaBGood
AmitCAverage
NehaDNeeds 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 IDStatus Code
ORD1011
ORD1022
ORD1033
ORD1044
ORD1055

Excel Formula:

=SWITCH(B2,1,"Pending",2,"Processing",3,"Shipped",4,"Delivered","Cancelled")

Expected Result:

Order IDStatus CodeOrder Status
ORD1011Pending
ORD1022Processing
ORD1033Shipped
ORD1044Delivered
ORD1055Cancelled

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 NumberMonthly Sales
145000
252000
348000
461000
557000
668000
772000
865000
975000
1082000
1179000
1295000

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 NumberMonthly SalesMonth Name
145000January
252000February
348000March
461000April
557000May
668000June
772000July
865000August
975000September
1082000October
1179000November
1295000December

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 IDDelivery Type
ORD201Standard
ORD202Express
ORD203Same Day
ORD204Pickup
ORD205Standard

Excel Formula:

=SWITCH(B2,"Standard",50,"Express",100,"Same Day",150,"Pickup",0,"Not Available")

Expected Result:

Order IDDelivery TypeDelivery Charge
ORD201Standard50
ORD202Express100
ORD203Same Day150
ORD204Pickup0
ORD205Standard50

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.

  1. Convert employee shift codes 1, 2, and 3 into Morning, Evening, and Night.
  2. Convert product category codes 10, 20, and 30 into Electronics, Clothing, and Furniture.
  3. Display payment descriptions for Cash, Card, UPI, and Bank Transfer.
  4. Convert customer types Regular, Premium, and VIP into their respective labels.
  5. 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:

EmployeeShift Code
Rahul1
Priya2
Amit3
Neha2
Karan1

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:

EmployeeShift CodeShift Name
Rahul1Morning Shift
Priya2Evening Shift
Amit3Night Shift
Neha2Evening Shift
Karan1Morning 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.