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.
Excel Switch Function (Table of Contents)
What is SWITCH Function in Excel?
SWITCH function lookup one value which is also called as an “Expression” against the list of values and will return the value which is corresponding to the expression that taken.
SWITCH Formula in Excel
- 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
Why SWITCH Function in Excel?
SWITCH Function in Excel is used to match the first value or expression from the list of possibilities available for us and returns the value which is corresponding to the matching value.
#1 – Example of SWITCH Function in Excel
Let’s take the data of a Real Estate project with following attributes: –
- One of the project name “Palm Court”
- Different blocks that are available in the project
- Different unit no’s that are available in the project for their respective blocks
- 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 Price Range column –
- SWITCH Function will be available in Excel 2016.
- In this example, we have to take 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 will it get the match to obtain result 2 and this step will continue till the expression matches with 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 IFs function, then we need to have a 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.
#2 – Example of VLOOKUP using SWITCH Function in Excel
VLOOKUP using SWITCH is one of the most important combinations to calculate from different tables. SWITCH is used with VLOOKUP to create a table array for different tables to look across.
Let’s go through the example below for clear understanding: –
We have 4 columns – ICECREAM, Type, Price & Discount.
We have 3 different tables for the type of ice creams.
Now, we have to calculate the discount percentage that Ice cream store is offering based on different conditions like the type of ice cream and their price range
As per the above image, we have got 3 different tables of a type of ice creams like cone, cup and family pack with the discounts they are offering for different prices.
Now, let’s see the formula that we used –
The above formula tells that –
By using the Excel SWITCH Function, we had created 3 different arrays for 3 different tables. The output from the 1st cell of Discount we can see price in C2 will be looked in the array that matches the type of ice cream in B2. As B2 is Cone, the array will be formed from SWITCH function for cone table and ignores other conditions. Once a table is created C2 will search for the range it falls from the categories that we had taken in cone table and takes 2nd column (Discount %) as we had mentioned 2 in our formula.
The output is showing 10% because the price 199 is falling in between 100/- & 200/-, but as the value is below 200/- and above 100/- it will consider the discount % of 100/- as the price didn’t cross 200/-.
Similarly, if we drag the cells down then the same type of comparisons and evaluation of conditions will be taken place for the rest of cells. We can see in the above picture: –
There will be few instances where we get #NA as shown below for the cases that don’t meet the given criteria.
In this example, we can observe that price range below 50 for the cone, below 200 for the cup and below 500 for the family pack are not available (can cross check with the above picture for the clear understanding indifference).
We can see cell D7 & D14 are showing #NA as these two prices are not in the range as per the tables that we considered.
How Different is SWITCH from IFs?
- Excel SWITCH function doesn’t allow us to use logical operators 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. 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 IF function, we have the default value in switch function.
Things to Remember about SWITCH Function in Excel
- SWITCH Function is introduced and available in Excel 2016 but not available in earlier versions of Excel and Excel 2016 in Mac.
- 126 pair 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 an expression, and cannot test if a value is larger or smaller.
- We have the default value in SWITCH Function which is not in 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 which can also be done by using CHOOSE function but SWITCH’s default argument makes it a better solution. It’s not possible to use logical operators in 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 –
- How to use SUMIF With VLOOKUP with Examples?
- Excel VBA Value
- SUMIF Between Two Dates
- How to Transpose in VBA?
- Examples of P-Value in Excel
- Use ISNA Function in Excel
- Use Substitute Function in Excel
- Use VALUE Function in Excel
- Use TRANSPOSE Excel Function
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
