WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Excel Tutorials » SWITCH Excel Function

SWITCH Excel Function

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

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 –

1 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>