Learn the use of Google Sheets SWITCH formula. Once learned, you can use this Logical Function to easily replace several IF or IFS functions. I have detailed how to use SWITCH function below and also compared it with IF, IFS and Nested IF.
We are obsessed with IF and IFS. So time to limit the use of them. Show your brilliance in choosing a Logical function. It can not only improve your spreadsheet performance, but also it can make your formula look clean. I strongly believe the SWITCH function is not yet much explored by Google Sheets as well as Excel enthusiasts.
I extensively use Google Sheets now a days and logical functions are always my best companion. I normally use IF Logical Function as it can easily accommodate OR, AND logical Functions with it. Also a Nested IF can meet your almost all Logical Test requirements on Google Spreadsheets.
But the problem with Nested IF is, it’s tough to follow in a future date. At the same time IFS and SWITCH are pretty easy to follow.
Though you cannot replace IF with IFS or SWITCH on all places, but at certain occasions the SWITCH formula is far more better than IF, IFS or Nested IF. So let us begin.
Note: I have mentioned many functions below other than SWITCH. If you are not yet learned any of them, simply switch to this page.
How to Use SWITCH Function in Google Sheets
SWITCH(expression, case1, value1, [case2, value2, …], [default])
- Expression – Any valid values. Example A1:A15
- Case1 – The first case to be checked against expression.
- Value1 – The value to be returned if Case1 matches expression.
- Case2, Value2, … – [OPTIONAL] – Same as Case1 and Value1 and these are purely optional.
- Default – [OPTIONAL] – An optional value to be returned if none of the cases match the expression.
Google Sheets Switch Formula Example
You can learn Google Sheets SWITCH function with one Single Example.
Please carefully go through the above image and compare it with SWITCH function Syntax. All the elements in the Syntax, I’ve marked on the sample SWITCH formula above. So you can easily grasp it. Also please note that Google Sheets SWITCH formula behaves like an array formula. Now I think, I can move to the comparison of SWITCH with IF and IFS. Here is the SWITCH vs. IF vs. IFS vs. Nested IF comparison.
Switch Formula Comparison with IF and IFS
As I already told you, SWITCH formula is one of the less explored logical function in Google Spreadsheets. But let me try to unveil few of its pros and cons in comparison with IF and IFS.
Where to use IF, IFS and SWITCH?
First of all, you should properly understand the purpose of these three functions. Then you can easily get an idea, when and where to use any of them. IF and IFS can’t use for same purposes. It’s applicable to SWITCH also. SWITCH function is actually a combination of IF and IFS but with certain limitations. I will come back to that. First make a clear picture of these three popular logical functions in your mind.
SWITCH(expression, case1, value1, [case2, value2, …], [default])
IF(logical_expression, value_if_true, value_if_false)
IFS(condition1, value1, [condition2, value2, …])
Logical Expression, Expression and Condition, quite confusing right?
What is Expression in Google Sheets?
A Cell or Cells in a Google Sheets may contain an expression or data. Data is the value you entered into a cell like text, numbers etc. But an Expression is a statement or formula which returns a value. Here expression can be reference to any valid value or values in a cell or range of cells.
What is Logical Expression in Google Sheets?
logical_expression – An expression or reference to a cell containing an expression that represents some Logical Value. Logical expressions have one of two values; True or False.
What is Condition in Google Sheets?
Condition can be a Boolean (also called Logical Value), a number, an array, or a reference to any of those.
By saying that I am going to one example where I’ve applied all the above three Logical functions. You could easily understand the concept of Expression, Logical Expression and Condition from the below examples.
In this example, for the first formula using SWITCH function; A2:A8 is the Expression. It’s valid values in a range of cell.
In the second formula using IFS function, A2=”Sunday” is the Condition 1 and A2=”Saturday” is the Condition 2. So Logical Values.
In the third formula using IF function, A2=”Sunday” is the Logical Expression or Logical Value.
Now to let us examine the formula result. In the above example, SWITCH function has an edge over IF and IFS.
SWITCH Tests an expression against a list of cases. Here expression is in the range A2:A8 and cases you can say, “Sunday”, “Monday”, “Tuesday” etc. Then it returns the corresponding value of the matching case. Here “Full Off” and “Half Working Day” are the values. If the test fails to find the matching case, the formula can return an optional default value and in this formula, that value is “Working Day”.
See the above example where I’ve applied the SWITCH formula in Cell B2. You can see the results expand to downwards like an ARRAYFORMULA.
IFS evaluates multiple conditions or logical tests like A2=”Sunday”, A2=”Saturday” and returns a value that is corresponding to the first True condition. The problem here is, if no conditions are met, it can’t immediately return a False value. As a result of this, when you change the value in cell A2 to “Monday”, IFS function will return #N/A that means no match error. The optional default value in SWITCH and the value_if_false part of the IF can address this issue.
Now we can move to the IF logical function. As mentioned above, IF function returns one value if a logical expression (A2=”Sunday”) is “TRUE” and another value, if it is “FALSE”. Here in this case, when you change the value in Cell A2 to “Monday”, the formula won’t return any error. The formula will execute the value of value_if_false part.
See how IF and IFS can match with Google Sheets SWITCH formula in the above examples. Here I’m going to modify the IF and IFS formula.
Let’s begin with IFS. Here, a combination of ARRAYFORMULA and OR logical function can match the result of SWITCH function. Below is the modified IFS formula where I have highlighted the changes which I’ve incorporated.
=ArrayFormula(IFS(A2:A=””,” “,A2:A=”Sunday”,”Full Off”,A2:A=”Saturday”,”Half Working Day”,OR(A2:A<>”Sunday”,A2:A<>”Saturday”),”Working Day”))
Here you can see the advantage of SWITCH over IFS logical function. SWITCH is much more smarter. Anybody can easily follow the SWITCH function in a future date as it’s simple to read. IFS became much complicated to understand. Now with IF.
Here you are helpless with IF! You should use Nested IF and ARRAYFORMULA combination to solve the issue. Now IF is also much smarter than IFS in this case. See the below formula.
=ArrayFormula(if(A2:A=”SUNDAY”,”Full Off”,if(A2:A=”SATURDAY”,”Half Working Day”,if(A2:A=””,””,”Working Day”))))
Why IFS become much complicated than IF and SWITCH?
The problem with IFS is, it doesn’t have an option to return a value if a test or condition fails. You may require to address such issue with another condition and value. That make the formula complicated.
SWITCH Looks Much Simpler and Easy to Use. Still Why Users Reluctant to Use It?
As I’ve mentioned at the beginning of this post, Google Sheets SWITCH formula is actually a combination of IF and IFS but with certain limitations. What are those limitations?
The main reason behind the less popularity of SWITCH is, as far as I am concerned, it doesn’t take Logical operators such as less than, greater than directly. That means the conditions must be exact matches in SWITCH. Though you can at some extent overcome these limitations with nested SWITCH or AND, OR, IF logical functions within SWITCH, nobody willing to do so. Because the same can easily be achieved by nested IF then why should one use Google Sheets SWITCH formula?
The main purpose of the use of SWITCH is to simplify the things. Then why should we go for complicated Nested SWITCH and all?
Advanced Use or Practical Use of Google Sheets SWITCH Formula
You can use Google Sheets SWITCH formula to identify duplicates in a data set. See my separate dedicated tutorial on this HERE. I’ve detailed different methods to find and remove duplicates in Google Spreadsheets There.
From my point of view, a Nested IF is much better than IFS and SWITCH functions. But certain places you can use IFS or SWITCH to simplify your formula. To do that you should know which function suits your needs. Hope I could shed some light into that part through this tutorial.