How to Use SWITCH Function in Google Sheets [Complete Guide]

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.

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

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!