What Is SWITCH Google Sheets Function?
The SWITCH Google Sheets Function compares and matches a selected cell value to a group of cells and returns the result, or replaces it, based on the first match found. Also, returns the specified default value, if there is no match found.
The SWITCH function in Google Sheets is used in place of various conditional functions, such as IF(), nested IF(), IFS(), CHOOSE(), etc. Thus, reducing the formula length.
For example, consider the value in cell A2. Let us switch or replace the data with the given default values in the formula using the Google Sheets SWITCH function.
Select cell B2, enter the formula =SWITCH(A2,“IND”,“INDIA”) and press “Enter”, as shown below.
The output is shown above, i.e., the selected cell value, IND, is replaced with the new value INDIA.
Key Takeaways
- The SWITCH Google Sheets Function helps users replace a cell value with a new value with based on the result’s published value. Now, these cell values can either be a cell reference or entered directly within double-quotes in the formula.
- It checks the supplied value against a list of values and returns the result corresponding to the first match found. Users can apply this function instead of nested IF, as it reduces the formula length and complexity while allowing one to test more than one condition.
- Alternate functions for the SWITCH() function are CHOOSE(), IF(), VLOOKUP(), VBA SWITCH, etc.
- The function determines an exact match for a given value. However, we can use it along with other function, such as MONTH() and WEEKDAY().
Syntax
The syntax of the SWITCH formula in Google Sheets is,
The arguments of the SWITCH formula in Google Sheets are,
- expression: It is the value or expression to check or match. It is a mandatory argument.
- case1: It is the first case to be checked against the expression. It is a mandatory argument.
- value1: It is the corresponding value to display or return if case1 matches the expression. It is a mandatory argument.
- [case2_or_default, …]: These are the additional cases to try if the previous ones do not match expression, or an optional default value to be returned if none of the cases match expression. It is an optional argument.
- [value2, …]: It is the additional values to be returned if their corresponding cases match expression. It is an optional argument.
How To Use SWITCH Google Sheets Function?
We can use the SWITCH Function in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access From the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow à click the “Logical” option right arrow → select the “SWITCH” function, as shown below.
Step 2: The “SWITCH” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =SWITCH( in the cell, as shown below. [Alternatively, type =S or =SW and double-click the SWITCH from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some SWITCH Google Sheets function examples along with other functions, such as IF(), MONTH(), WEEKDAY(), etc.
Example #1
The dataset given below consists of some grocery items and their category ID’s. We will switch the ID’s with the category name according to the table in cells F1:G6.
The steps to replace the category ID’s with their names using the SWITCH() are as follows:
Step 1: Select cell C2 and enter the formula =SWITCH(B2,1,”Vegetables”,2,”Pulses”,3,”Grains”,4,”Dairy Products”,5,”Fruits”,”Not Found”), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C12 using the fill handle, to get the following results.
Example #2 – Using SWITCH with MONTH.
Consider the dataset with some dates. Let us replace the month number with the name of the month using SWITCH with MONTH and also using the table in cells E1:F13. We have derived the month from the dates in column A for our reference.
The steps to replace the month number with their names using the SWITCH() are as follows:
Step 1: Select cell C2 and enter the formula =SWITCH(MONTH(B2),1,”Jan”,2,”Feb”,3,”Mar”,4,”Apr”,5,”May”,6,”Jun”,7,”Jul”,8,”Aug”,9,”Sep”,10,”Oct”,11,”Nov”,12,”Dec”,”Not Found”), as shown below.
Step 2: Press “Enter” and Google Sheets gives the autofill option, as shown below. We can either select it or use the fill handle option.
Step 3: Choose the tick mark to get the results auto filled, as shown below.
Example #3 – Using SWITCH with WEEKDAY.
The dataset given below consists of some dates. Let us replace the weekday with the weekday name of the week using SWITCH with WEEKDAY and also using the table in cells F1:G8. We have derived the weekdays from the dates in column C for our reference.
The steps to replace the weekday with their names using the SWITCH() are as follows:
Step 1: Select cell B2 and enter the formula =SWITCH(WEEKDAY(A2),1,”Mon”,2,”Tue”,3,”Wed”,4,”Thu”,5,”Fri”,6,”Sat”,7,”Sun”,“Not Found”), as shown below.
Step 2: Press “Enter” and drag the formula from cell B2 to B12 using the fill handle, to get the following results.
Differences Between SWITCH And IF Functions
A few differences between the SWITCH and the IF functions are as follows:
- The SWITCH function does not allow us to use logical operators like greater than (>) / less than (<) to match the cases, where there is no exact match.
- The expression will appear only once in the Google Sheets SWITCH function, but in the IF’s function, the expression needs to be repeated.
- The SWITCH function’s length is less than the IF’s function, making it easier to read and create, as it allows us to test multiple criterias.
- It is more like a CHOOSE function in Google Sheets. It has an expression that is a lookup value. We would match it to value 1 and value 2 and get the result value. Unlike the IF function, we have the default value in the SWITCH function.
Important Things To Note
- When the SWITCH Google Sheets function cannot find a match for the supplied expression, and there is no default value, the function returns a #N/A error.
- However, as a negative, we cannot use logical operators such as >, <, or = in the expression. It simply matches the values in its list to the expression and cannot test if a value is larger or smaller.
- We should ensure that the argument expression in the SWITCH() is a number, text value, cell reference, named range, Boolean value, function, or formula.
- For every n-value argument supplied, there should be a corresponding n-result argument.
Frequently Asked Questions
A few reasons the SWITCH Google Sheets function may not work are as follows:
a. We have not mentioned the default argument, so we got the #NA error.
b. We have dragged the formula to get the cell results of the other cell values without inserting the proper replacement value. Once again, we get an #NA error.
c. The SWITCH() argument expression is not a number, text value, cell reference, named range, Boolean value, function, or formula.
d. A corresponding n-result value for every n-value argument is not provided to the SWITCH().
e. The SWITCH() did not find a match for the given value, and the default argument is not provided.
The SWITCH Google Sheets function is different from IFS() in the following ways:
• The argument expression in the SWITCH() cannot contain logical operators and can appear only once as an argument. On the other hand, the condition1 argument in IFS() (the equivalent of expression) can contain logical operators, and the function can have more than one condition argument.
• The SWITCH() is easier to create and less complex than the IFS(). Also, the formula created using the SWITCH() will be less lengthy than the one created using IFS(), and the formula is more straightforward and compact than the IFS().
We often forget in which category a function falls, here, the “SWITCH” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “SWITCH” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the SWITCH Google Sheets Function by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “SWITCH” function, as shown below.
Download Template
This article must help understand SWITCH Google Sheets function with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is SWITCH Google Sheets Function. We learn its syntax & how to use it to replace a cell value based on a published value. You can learn more from the following articles. –
Leave a Reply