How to Use SWITCH Function in Excel? (with Examples)

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

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

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 –

Popular Course in this category

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

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.

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

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 an expression, and cannot test if a value is larger or smaller.
We have the default value in the 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 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.