Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Use the SWITCH Function in Excel (With Examples)

Learn how to use the SWITCH function in Excel with practical examples, formula syntax, and tips to replace nested IF statements and simplify your form

 

How to Use the SWITCH Function in Excel (With Examples)

   Learn how to use the SWITCH function in Excel to compare multiple values and return matching results. Explore formula syntax, practical examples, common errors, and how SWITCH compares with IF and IFS functions.

How to Use the SWITCH Function in Excel: A Complete Guide with Examples

Excel offers several functions that make it easier to manage data, automate calculations, and reduce repetitive work. One useful function for handling multiple conditions is the SWITCH function.

If you have ever written a long IF formula to return different results based on a cell's value, SWITCH can make your formula shorter and easier to understand.

For example, imagine you maintain a sales report where each salesperson has a performance rating such as Excellent, Good, Average, or Poor. Instead of writing several nested IF functions, you can use SWITCH to return the appropriate result based on the rating.

In this guide, you will learn how the SWITCH function works, its syntax, practical examples, common mistakes, and situations where it can make your Excel worksheets more efficient.

What Is the SWITCH Function in Excel?

The SWITCH function in Excel compares one value against a list of specified values and returns the result associated with the first matching value.

If Excel cannot find a match, it can return a default result that you specify.

It is particularly useful when a single cell contains different possible values, and you want Excel to display a corresponding result for each one.

For example:

  • If a cell contains A, return Excellent.

  • If it contains B, return Good.

  • If it contains C, return Average.

  • If it contains D, return Needs Improvement.

You can handle all these possibilities with one SWITCH formula instead of creating multiple nested IF statements.

SWITCH Function Syntax in Excel

Before using this function, it is important to understand its syntax.

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

Here is what each argument means:

Argument

Description

expression

The value or cell reference Excel needs to evaluate.

value1

The first value Excel compares with the expression.

result1

The result returned if the first value matches.

value2

An additional value to compare.

result2

The result returned if the second value matches.

default

An optional result returned when no value matches.

The expression is compared with each specified value in order. Excel returns the result for the first match it finds.

If you do not specify a default result and no value matches, Excel returns the #N/A error.

How to Use the SWITCH Function in Excel

Let's understand how this function works with a simple example.

Suppose you have a list of product categories in column A, and you want Excel to display a corresponding department name in column B.

Sample data

Product Category

Department

Electronics

Technology

Clothing

Fashion

Furniture

Home

Grocery

Food

Enter the following formula in cell B2:

=SWITCH(A2,"Electronics","Technology","Clothing","Fashion","Furniture","Home","Grocery","Food","Other")

Press Enter.

Excel checks the value in A2 and returns the corresponding department name.

For example, if A2 contains Electronics, the formula returns Technology. If A2 contains Grocery, it returns Food.

The final argument, Other, acts as the default result. It prevents the formula from returning #N/A when the category is not included in the list.

You can drag the formula down to apply it to the remaining rows.

Practical Examples of the SWITCH Function in Excel

The best way to understand SWITCH is to see how it can solve everyday spreadsheet problems.

Here are some useful examples you can try in your own workbook.

Convert Numeric Values into Text Labels

Suppose you maintain an employee performance sheet where employees receive scores from 1 to 4.

You want Excel to convert those scores into readable performance labels.

Score

Performance

1

Poor

2

Average

3

Good

4

Excellent

Use this formula in cell B2:

=SWITCH(A2,1,"Poor",2,"Average",3,"Good",4,"Excellent","Invalid Score")

If A2 contains 3, Excel returns Good.

If someone enters 5 or another unlisted value, the formula displays Invalid Score.

This approach is useful for employee evaluations, customer feedback, and performance reports.

Calculate Commission Based on Sales Category

Imagine you run a sales business where commission depends on the sales category assigned to each employee.

Your commission rules are:

Sales Category

Commission Rate

Bronze

2%

Silver

4%

Gold

6%

Platinum

10%

If the sales category is in A2, use:

=SWITCH(A2,"Bronze",2%,"Silver",4%,"Gold",6%,"Platinum",10%,0%)

Suppose the employee belongs to the Gold category and has generated sales of $5,000.

You can calculate the commission using:

=B2*SWITCH(A2,"Bronze",2%,"Silver",4%,"Gold",6%,"Platinum",10%,0%)

If A2 contains Gold and B2 contains 5000, the result is $300.

This is a practical way to automate commission calculations without creating multiple IF conditions.

Assign Grades Based on Exam Results

You can also use SWITCH to assign grades when your worksheet already contains a grade code.

For example, suppose a student receives a grade code in cell A2.

Grade Code

Final Grade

A

Excellent

B

Very Good

C

Good

D

Pass

F

Fail

Enter this formula:

=SWITCH(A2,"A","Excellent","B","Very Good","C","Good","D","Pass","F","Fail","Invalid Grade")

Excel checks the grade code and returns the corresponding description.

This can save time when preparing student records, examination reports, and academic summaries.

Important: SWITCH matches specific values. If you want to assign grades directly from numerical marks, such as 90 and above for A or 80–89 for B, an IFS function is generally more appropriate.

Display Different Messages Based on Payment Status

Suppose you manage customer payments and maintain a status column in Excel.

You want to display a message based on whether a payment is Completed, Pending, or Failed.

Payment Status

Message

Completed

Payment Received

Pending

Awaiting Payment

Failed

Payment Unsuccessful

Use the following formula:

=SWITCH(A2,"Completed","Payment Received","Pending","Awaiting Payment","Failed","Payment Unsuccessful","Check Payment Status")

This formula automatically displays the appropriate message.

It can be useful for invoice tracking, customer payment records, and business accounting sheets.

Use SWITCH with the WEEKDAY Function

SWITCH can also work with other Excel functions.

For example, suppose you want to display the name of the day based on a date entered in cell A2.

Use:

=SWITCH(WEEKDAY(A2),1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday",5,"Thursday",6,"Friday",7,"Saturday")

The WEEKDAY function returns a number representing the day of the week. SWITCH then converts that number into its corresponding day name.

For example, if A2 contains a date that falls on Monday, the formula returns Monday.

This is helpful when preparing attendance sheets, work schedules, and daily business reports.

Use SWITCH with Other Excel Functions

One of the useful features of SWITCH is that you can combine it with other Excel functions to create more flexible formulas.

For example, you can combine SWITCH with UPPER to handle category names regardless of whether they are entered in uppercase or lowercase.

=SWITCH(UPPER(A2),"YES","Approved","NO","Rejected","PENDING","Under Review","Unknown")

If A2 contains yes, YES, or Yes, Excel converts the text to uppercase before checking the conditions.

You can also combine SWITCH with TRIM to remove unwanted spaces:

=SWITCH(TRIM(A2),"Paid","Completed","Unpaid","Pending","Overdue","Late","Unknown")

This is especially useful when working with imported data or information entered manually by multiple people.

SWITCH vs IF vs IFS in Excel

Excel provides several functions for handling different conditions. However, each function works slightly differently.

Feature

SWITCH

IF

IFS

Main purpose

Match one expression against specific values

Test a logical condition

Test multiple logical conditions

Multiple conditions

Yes, using value-result pairs

Yes, through nesting

Yes

Best for exact matches

Excellent

Good

Good

Supports ranges

No, not directly

Yes

Yes

Default result

Optional default argument

Else argument

Can use TRUE as final condition

Formula readability

Good for multiple exact matches

Can become complex

Good for multiple conditions

Which function should you use?

  • Use SWITCH when you need to compare one value against several specific options.

  • Use IF when you need to test a simple condition, such as whether sales are greater than a target.

  • Use IFS when you need to evaluate multiple logical conditions, such as assigning grades based on numerical score ranges.

Choosing the right function can make your formulas easier to maintain and troubleshoot.

Common SWITCH Function Errors in Excel

Although SWITCH is relatively easy to use, a few mistakes can cause unexpected results.

Missing Default Result

If none of the specified values matches the expression and you have not provided a default result, Excel returns #N/A.

Solution: Add a default value at the end of the formula.

=SWITCH(A2,"Yes","Approved","No","Rejected","Unknown")

Using SWITCH for Range-Based Conditions

SWITCH is designed for matching specific values, not checking whether a number falls within a particular range.

For example, it is not the right choice for conditions such as:

  • Sales above 10,000

  • Sales between 5,000 and 10,000

  • Sales below 5,000

Solution: Use IFS instead.

=IFS(A2>=10000,"High",A2>=5000,"Medium",A2<5000,"Low")

Extra Spaces in Text Values

Sometimes a formula does not return the expected result because the source data contains unwanted spaces.

For example, Excel may treat Paid and Paid with an extra trailing space as different text values.

Solution: Use TRIM to clean the input.

=SWITCH(TRIM(A2),"Paid","Completed","Unpaid","Pending","Unknown")

Forgetting to Match Data Types

Text values and numbers are not interchangeable in SWITCH comparisons.

For example, the number 1 and the text "1" are different values.

Solution: Check the data type in your source cells and use matching values in your formula.

Which Excel Versions Support the SWITCH Function?

The SWITCH function is available in modern versions of Microsoft Excel.

Excel Version

SWITCH Support

Microsoft 365

Yes

Excel 2024

Yes

Excel 2021

Yes

Excel 2019

Yes

Excel 2016

No

Excel 2013

No

Excel 2010

No

If you are using an older Excel version that does not support SWITCH, you can achieve similar results using nested IF functions.

Frequently Asked Questions (FAQs)

What does the SWITCH function do in Excel?

The SWITCH function compares one value against multiple specified values and returns the result associated with the first match.

What happens if SWITCH does not find a match?

If you specify a default result, Excel returns that result. Otherwise, it returns the #N/A error.

Can SWITCH replace nested IF functions?

Yes. SWITCH can replace many nested IF formulas when you are checking one expression against several exact values.

Can SWITCH check number ranges?

No. SWITCH is designed for exact-value matching. Use IFS or IF for range-based conditions.

Is the SWITCH function available in Excel 2016?

No. SWITCH is supported in Excel 2019 and later versions, including Microsoft 365, Excel 2021, and Excel 2024.

Final Thoughts

The SWITCH function is a useful Excel tool when you need to return different results based on a single value. It can simplify formulas that would otherwise require several nested IF statements.

Whether you are managing employee records, calculating commissions, preparing payment reports, or organizing business data, SWITCH can make your worksheets easier to read and maintain.

The key is to use it for exact-value comparisons rather than complex logical conditions or numerical ranges. Once you understand its syntax, you can combine SWITCH with other Excel functions to automate many everyday spreadsheet tasks.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content