What is the SWITCH Function in Excel?

For example, we want to replace IND(cell C10) with INDIA, CA (cell C11) with CALIFORNIA, and SA(cell C12) with SOUTH AFRICA. Then, the SWITCH function can easily do the job in such a scenario. We can use the following formula:

=SWITCH(C10,”IND”,”INDIA”, “CA”, “CALIFORNIA”, “SA”, “SOUTH AFRICA”) 

Syntax

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

Example

  • One of the projects is the name “Palm Court.”Different blocks that are available in the projectOther unit numbers are available in the project for their respective blocks.It is the “Price Range” that we are supposed to determine as per the assumption taken.

Switch Formula

Let us look at the SWITCH formula to obtain the value of cell 2 in the “Price Range” column:

  • The SWITCH function is 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 and Result 1 – We have mentioned that if an expression matches value 1, then take result 1 or go to condition 2.Value 2 and Result 2 – If condition 1 is not satisfied, then the expression comes to condition 2 to check whether it will get the match to obtain result 2. This step will continue until the expression matches the value.If the expression does not match any value in the conditions, it will give NA as output (this can be seen in the below image).

To mention some statements 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, we need to have an Excel nested IFExcel Nested IFIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more for “True” or “False,” which is not a LOOKUP function. Only when it is “True” gives out one result; for “False,” it gives other results.

How Different is SWITCH from IFs?

  • The SWITCH function does not allow us to use logical operators in excelLogical Operators In ExcelLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more like greater than (>) / less than (<) to match the cases where there will not be an exact match.The expression will appear only once in the Excel SWITCH function, but in the IFS function, the expression needs to be repeated.The SWITCH function’s length is less than the IFS function, making it 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 that is lookup value. We would match it to value 1 and value 2 and get the result value. Unlike the IF function, we have the default value in the SWITCH function.

Things to Remember

  • The SWITCH function is introduced and available in Excel 2016 but not in earlier versions of Excel and Excel 2016 on Mac.We can take 126 pairs of values and results into the Excel SWITCH function.The SWITCH function in Excel will return the #NA error if we do not mention any default condition and if any condition does not 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.The SWITCH function in Excel is unlike VLOOKUP, but we can use the SWITCH function in the VLOOKUP function.

Conclusion

The SWITCH function is available in Excel 2016. It is the most powerful function instead of the IFS function. We can use this function in the Excel VLOOKUP function to create an array that we can also do by using the CHOOSE function. But, the SWITCH function’s default argument makes it a better solution. It is not possible to use logical operators in the SWITCH function.

This article is a guide to SWITCH in Excel. Here, we discuss using the SWITCH function in Excel with IFs and VLOOKUP functions, along with examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Calculate P-Value in ExcelCalculate P-Value In ExcelP-value is used in correlation and regression analysis in Excel to determine whether the result obtained is feasible or not and which data set from the result to work with. It’s value ranges from 0 to 1.read moreISNA Function in ExcelVALUE Function in ExcelTRANSPOSE Function in ExcelTRANSPOSE Function In ExcelThe TRANSPOSE function in excel helps rotate (switch) the values from rows to columns and vice versa. Being a part of the Excel lookup and reference functions, its purpose is to organize the data in the desired format. To execute the formula, the exact size of the range to be transposed is selected and the CSE key (“Control+Shift+Enter”) is pressed.
  • read more