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

• 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

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 Formula

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).

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

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 excelLogical Operators In ExcelIn Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more 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 ExcelCHOOSE Function In ExcelChoose Function returns a value from the list of values in a given range. This function takes two mandatory arguments: the index number and the first value. The other values are optional to mention.read more. 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