WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » SWITCH Excel Function

SWITCH Excel Function

What is SWITCH Function in Excel?

Switch function in excel is a comparison and referencing function in excel which compares and matches a referred cell to a group of cells and returns the result based on the first match found, the method to use this function is as follows =SWITCH( target cell, value 1, result 1….), based on the value the result is published.

Syntax

SWITCH Function Example 1

  • Expression Value – The value or expression to match against
  • Value1/Result1 – The first value and result pair
  • Value2/Result2 – The second value and result pair (This is optional)
  • Default – Default value to use when no match is found

Example

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

SWITCH Function Example 1-1

  1. One of the project name “Palm Court.”
  2. Different blocks that are available in the project
  3. Different unit no’s that are available in the project for their respective blocks
  4. This is the “Price Range” that we are supposed to determine as per the assumption is taken

SWITCH Function Example 1-2

Switch Formula

SWITCH Function Example 1-3

Let’s look at the Switch formula to obtain the value of cell 2 in the Price Range column –

  • SWITCH Function will be available in Excel 2016.
  • In this example, we have to take the block cell as the expression as the assumptions of prices are based on block numbers.
  • Value1 & Result 1 – We have mentioned the condition that if an expression is matching with Value 1, then take the Result 1 or else go to condition 2.
  • Value 2 & Result 2 – If condition 1 doesn’t satisfy, then expression comes to condition 2 to check whether it will get the match to obtain result 2, and this step will continue till the expression matches the value.
  • If the expression doesn’t match with any value in the conditions, then by default, it will give NA as output (this can be seen in the below image).

SWITCH Function Example 1-4

In order to mention some statement for the cells that are showing #NA, we can give a string within inverted commas as below –

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

SWITCH Function Example 1-5

If we evaluate the above problem using the IFs function, then we need to have an Excel nested IF for true or false, which is not a lookup function. Only when it is true, it gives out one result and for false other results.

How Different is SWITCH from IFs?

  • SWITCH function doesn’t allow us to use logical operators in excel like greater than (>) / less than (<) in order to match the cases where there will not be an exact match.
  • In Excel SWITCH Function, the expression will appear only once, but in IFS function, the expression needs to be repeated.
  • The length of the SWITCH Function is less compare to the IFS Function, easier to read and create as it allows us to test more than one condition.
  • It is more like a CHOOSE function in Excel. It has an expression which is lookup value, and we would be matching it to value 1, value 2, and get the result value. Unlike the IF function, we have the default value in the switch function.

Things to Remember

  • SWITCH Function is introduced and available in Excel 2016 but not available in earlier versions of Excel and Excel 2016 in Mac.
  • 126 pairs of values & results can be taken into Excel SWITCH function.
  • SWITCH Function in excel will return the #NA error if we don’t mention any default condition and if any of the condition doesn’t get the match.
  • However, as a negative, we cannot use logical operators such as >, < or = in the expression. It simply matches the values in its list to expression and cannot test if a value is larger or smaller.
  • We have the default value in the SWITCH Function, which is not in the IF Function.
  • SWITCH Function in Excel is not like VLOOKUP, but SWITCH can be used in VLOOKUP.

Conclusion

SWITCH function is available in Excel 2016, and this is the most powerful function instead of IFS. This can be used in VLOOKUP to create an array that can also be done by using the CHOOSE function, but SWITCH’s default argument makes it a better solution. It’s not possible to use logical operators in the SWITCH function.

Recommended Articles

This has been a guide to SWITCH in Excel. Here we discuss how to use Switch Function in Excel and with IFs and VLOOKUPs along with examples and downloadable excel templates. You may also look at these useful functions in excel –

  • Calculate P-Value in Excel
  • ISNA Function in Excel
  • VALUE Function in Excel
  • TRANSPOSE Function in Excel
1 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download Switch Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More