EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Functions Excel SWITCH Function
 

Excel SWITCH Function

Jeevan A Y
Article byJeevan A Y
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 6, 2023

SWITCH Function example

 

 

SWITCH Function in Excel

The switch function in Excel compares one value with the values of another list and returns the first matched value if found. This function also considers and allows the user to enter the optional default value if no match is found. In other words, the Switch function matches the multiple values but returns only the first value out of the selected range. This is quite useful when we have duplicate values and want only the first value returned out of those.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

The Syntax of the SWITCH function includes 4 parameters. Out of 4 parameters of SWITCH functions, 3 are mandatory, and the fourth one is optional based on our requirements.

SWITCH Function example 1-1

  • Expression: This is simply the value or cell we are trying to test the logic.
  • Value1: This the test value or logical value comparing to the Expression. We can give 126 logical tests.
  • Result1: If the first logical test is true, what should be the result we want
  • Default or Value 2: Here, we can see two things. One is Default; if the first logical test is FALSE, what is the default value we want The second one is Value2; this is the second logical test for the Expression.
  • Result 2: This is the result of the second logical test we are testing.

How to Use the SWITCH Function in Excel?

Using the SWITCH Function in Excel is very easy. Let’s understand the Function in Excel through some examples given below.

You can download this SWITCH Function Excel Template here – SWITCH Function Excel Template

SWITCH Function in Excel – Example #1

As I said at the beginning, SWITCH is an alternative to the IF condition in Excel. We will now see how the simple IF condition works and compare the SWITCH with the IF condition in Excel.

I have a few fruit names here. I have 4 fruit names from cells A2 to A5 in Excel.

SWITCH Function example 1-2

Each fruit has its own status here, based on how they impact human body temperature. Let me apply the IF condition to test the results. The formula for this method is :

=IF (A2=”Apple”, “Medium”, IF(A2=”orange”, “Cold”, IF(A2=”Sapota”, “Heat”, IF(A2=”Watermelon”, “Cold”, “???”))))

IF function tests if the value in cell A2 equals Apple, the result should be Medium. If the result is false, then again one more IF condition will test if the value in cell A2 is equal to Orange; the result should be Cold; if the result is again false, then one more IF condition will test if the value in the cell A2 is equal to Sapota then the value will be Heat and so on. Finally, if none of the conditions is TRUE, then it will return the result as ???

Result

Like this IF condition tests multiple criteria and gives the result based on the logical tests, in the above example, we have applied 4 different IF conditions to get the result; this is why we call it a nested IF condition.

However, the SWITCH function can replace those many IF conditions and give the result in one formula.

The formula for SWITCH Function is:

=SWITCH (A2, “Apple”, “Medium”, “Orange”, “Cold”, “Sapota”, “Heat”, “Watermelon”, “Cold”, “???”)

Formula for SWITCH Fuction

Now, look at the above results from the SWITCH function; it is the same as the IF condition. Let me break down the formula into pieces for better understanding.

=SWITCH(A2, “Apple”, “Medium”, : This is the first argument of the function. If the cell A2 value equals Apple, it will get the result as Medium.

“Orange”, “Cold”: Unlike the IF condition, we need not put one more SWITCH condition again here; instead, we can go on in the formula. Furthermore, we need to mention the expression here (A2 cell) rather than enter the condition immediately. If the cell A2 value is equal to Orange, the result should be Cold.

“Sapota”, “Heat”: If the cell A2 value equals Sapota, the result should be Heat.

“Watermelon”, “Cold”: If the cell A2 value equals Watermelon, the result should be Cold.

“???”) : This is the final part of the formula. If none of the above 4 conditions is satisfied, then the result will be ???

Like this, the SWITCH function can be an alternative to nested IF conditions. But the SWITCH function has its limitation, too; we will see that limitation in the below section.

SWITCH Function in Excel – Example #2

The problem with the SWITCH function is limited to exact matching only. It cannot use any kind of operators in the formulas. Some of the common operators are >=(greater than or equal to), <=(less than to), etc.

Take a look at the example now. Typical examination example, I have scores and criteria.

I will apply the IF formula to determine the results.

For this, apply the formula given below:

=IF(A2>=85, “Distinction”, IF(A2>=75, “First Class”, IF(A2>=60, “Second Class”, IF(A2>=35, “Pass”, “Fail”))))

Formula Applied

But the SWITCH function cannot accept operator symbols to determine the result, and still, we have to rely on nested IF conditions to get the results.

Result

Things to Remember

  • The SWITCH Function in Excel works only for exact matching.
  • In the case of the operator, a symbol, to determine the logic, we have to rely on IF conditions.
  • SWITCH Function in Excel can handle 126 logical tests and results, but IF is limited to 64 conditions.
  • Using the SWITCH Function in exact match scenarios or using nested IF conditions is recommended.

Recommended Articles

This has been a guide to SWITCH Function in Excel. We discuss using the SWITCH Function in Excel and its limitations, practical examples, and a downloadable Excel template. You can also go through our other suggested articles–

  1. IFERROR Function in Excel
  2. Switching Columns in Excel
  3. Logical Functions in Excel
  4. VBA Switch

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download SWITCH Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download SWITCH Function Excel Template

EDUCBA

डाउनलोड SWITCH Function Excel Template

🚀 Limited Time Offer! - ENROLL NOW