Hi, today we will learn how to use SWITCH function in Google Sheets. The switch is a new addition in Sheets, and it is yet another function from the programming languages. The switch can be seen in JavaScript, Python, and C++. It’s one of the alternatives to IF family but has some benefits over IFS. So, today, we are going to use it and we will learn it by comparing its syntax with IF syntax for some examples. SWITCH is working like a switch, it has various cases, and based on the logic it performs a single operation.
For example, you have given 7-day names to a Switch function and have assigned numbers from 1 to 7 to each day, now you want to call a single day so you will define cases that will contain the numbers corresponding to each day’s name, and it will return a day. And, if you have entered an invalid number, it will return a default case which can be a custom message such as “Invalid day number” etc. So, today let’s practically learn how to use SWITCH function in Google Sheets.
Table of Contents
Importance of using Switch Function in Google Sheets
When we have to work with conditional statements, we use the IF function or IFS function, or sometimes we prefer COUNTIF functions, but there are some use cases when these functions become very long, and the syntax becomes difficult to write and understand by the viewer. So, to solve the long syntax issue, we have SWITCH, Switch has a smaller syntax and can be understood pretty easily, it does not have so many things in its syntax, and we mainly have cases, and cell references.
So, when we are working with the comparison of let’s say 10 cells, the IFS syntax may go to 5-6 lines, but the SWITCH syntax will be finished within 1-2 lines, so this is the biggest reason to learn SWITCH function in Google Sheets. SWITCH has more benefits than IFS, in the switch, we don’t have to define the cells again and again for the comparison, we only need to define the case once, and it will check it on its own. So, having that said, let’s get into it and learn how to use SWITCH function in Google Sheets.
How to Use Switch Function in Google Sheets?
From this section, we will learn how to use Switch function in Google Sheets. We will see some use cases and some examples of Switch function implementation, and in the last, we will compare a switch example with the IF or IFS function. So, without further delay, let’s get started.
Switch Function in Google Sheets – Simple Usage
In this section, we will learn how to use Switch function in Google Sheets and we will see a simple usage of the switch function along with its syntax. So, first, let’s see the syntax and then we will move to an example and implement it with the help of some dummy data.
Syntax of the SWITCH Function
SWITCH
Checks an expression against some cases and returns the corresponding value of the first matching case, with a default value if nothing else is matched. Not giving the default value will not lead to any error until there is no match.
Syntax
SWITCH(expression, case1, value1, [case2, value2, …], [default])
- expression – Any valid value or cell references.
- case1 – The first case that you want to check against the above experience
- value1 – The corresponding value for case 1 if it’s a match, otherwise it will be omitted in the result.
- case2, value2 – They are optional. They are additional cases and values that should be checked after the first case, they will be returned if matched, and the first case if not matched.
- default – It’s an optional argument but recommended: It’s an optional value, specified at the end of your Switch function. This is returned when there are no matches from the above case. Note that your function will work if you skip this, but it will return an error if no match case is found. Adding this value will stop errors, and will return the message that you specified as the default value.
Simple Usage
After understanding the syntax, let’s see a practical example of the switch function. So, I have sample data for my function, and we will use it to understand the switch function.
Step 1
Sample data
My scenario is that I have some students’ names and ranks, I will pass them in the switch function, and look for the student who has got the first position. Let’s see what my function returns.
Step 2
Write the SWITCH function
Step 3
Now firstly, pass the expression which can be a valid value or cell reference. So, I am adding here a cell reference in which I will search the student name for the corresponding rank
Step 4
Now, pass the first case value, which means the value that will be matched to your search
Step 5
To make the values in pairs, pass the first value for the first case
Step 6
Now the mandatory arguments are finished, now you repeat steps 3 and 4 until your values are finished.
Step 7
Now, add a default value in double quotes
=SWITCH(D2,A2,B2,A3,B3,A4,B4,A5,B5,A6,B6,”Invalid Input”)
Step 8
Now search in the search box to get results
Step 9
You can see we are getting students’ ranks by searching their names
Note: If you want to revert it, you can change it to search with Ranks and student names.
Step 10
We can just change the case and values by position to revert this function
Step 11
Now you can see, we are getting student names by searching their ranks.
This is how the switch function works, we have defined the name and ranks as cases and values and now we are getting corresponding values.
Tip: You can also do this without any sample data, you can define everything inside the function.
Step 12
To keep all the data inside the function body, you can see the below method
The formula will become
=SWITCH(D2,”first”,”alex”,”second”,”robin”,”third”,”farhen”,”fourth”,”robert”,”fifth”,”maddy”,”Invalid input”)
This works very same as the previous one, so this is how to use Switch function in Google Sheets for simple usage.
How to Use Switch Function in Google Sheets – The Default Case
In this section, we will learn how to use Switch function in Google Sheets, and we will see the default case. In the above example, we have not seen a default case, but I added it to avoid any errors. In this section, we will give some invalid inputs and see what happens with and without the default case.
Step 1
I am using the same examples with data outside the function
Step 2
Now, I am removing the default value from the function body
Step 3
Here, I am adding a wrong input
Step 4
You can see the function is returning an #N/A error that says “No match“.
Step 5
Now, let’s keep everything the same, but add a default case to the function body
Step 6
Now you can see it’s not returning an error, but a meaningful message without an error pop-up
This is a batter kind of error, which is not looking like an error, so it is very good for the users or viewers. That’s why I always recommend not omitting the default case.
How to Use Switch Function in Google Sheets – Another Example
In this section, we will learn how to use Switch function in Google Sheets, and we will see another use case and its example. So, let’s get started with the sample data.
Step 1
Sample data
Step 2
We have given activities for each day of the week
Now, I want to put the day name and get its corresponding activity, so I will add a similar Switch function formula here
Step 3
This is how it works; you can see I am getting the right activity for each day
Note: The data is not case sensitive i.e., Tuesday is the same as Tuesday or TUESDAY
This is how to use switch function in Google Sheets. I hope you find this tutorial helpful.
How to Use Switch Function in Google Sheets – IF vs SWITCH
In this section, we will learn how to use switch function and we will see a comparison between the IF and SWITCH functions. Well, technically SWITCH can be compared with IFS not IF.
IF function works similarly to the SWITCH, but unlike SWITCH, it can be used with logical expressions such as “less than” or “greater than” or “equal to“. While Switch can only be used with equal logic, that is like a2=4.
If you want to perform logical checking on your data, use the IF function. But for only equal logic or direct comparison, it’s better to use SWITCH because it has smaller and easier syntax. It can be a helpful function when you are directly comparing values and want to omit difficult and more complex syntax.
The formula for the last section we performed with SWITCH
=SWITCH(C8,A7,B7,A8,B8,A9,B9,A10,B10,A11,B11,A12,B12,A13,B13,”Invalid”)
Same formula using IF
=IF(C7=A7,B7,if(C7=A8,B8,IF(C7=A9,B9,IF(C7=A10,B10,IF(C7=A11,B11,IF(C7=A12,B12,IF(C7=A13,B13,”Invalid”)))))))
Important Notes
- The Switch function values and cases are not case sensitive
- Sometimes, we mistakenly add a space after the word we typed in a cell, which can cause a mismatch, so make sure you don’t have empty spaces after the word completed
- The switch can be alternatively used with IF, and IFS functions, but the syntax will be very big
- The switch function cannot work with logical expressions such as “greater than” and “less than“
Frequently Asked Quesitons
Can the SORTN function in Google Sheets be used in a similar way to the SWITCH function?
The sortn function in google sheets is different from the SWITCH function in terms of functionality. While the SWITCH function allows users to specify multiple conditions and their corresponding outputs, the sortn function is used to sort a range and return the specified number of top or bottom values. Therefore, the sortn function in Google Sheets cannot be used in a similar way to the SWITCH function.
How to use SWITCH function in Google Sheets?
We have a simple syntax for the SWITCH function that has an expression, and case corresponding to values. We can use so many cases and values in pairs only. The switch works very similarly to IF and IFS, so we can get the values for matching cases with a default case that occurs when no match is found. The default is the same as the Else statement in the IF function.
Conclusion
That’s from Switch function in Google Sheets. We learned how to use switch function in Google Sheets from the scratch, we learned the syntax first, we talked about the default argument, and then we saw multiple examples to understand the nature of the SWITCH function in Google Sheets. Then we discussed its cons and pros over the IF function, so I am closing this tutorial here hoping that you have learned this function pretty well. I will see you soon with another useful guide. Thank you!