This tutorial explains how to use the SWITCH function in Google Sheets and compares it with IFS and IF, two other logical functions. The primary focus will be on understanding the SWITCH function and its application.
The SWITCH function evaluates a value against a list of cases and returns the corresponding value of the first matching case. If none of the values match, the formula returns #N/A, which can be replaced with a default value.
SWITCH Function: Syntax and Arguments
Syntax:
SWITCH(expression, case1, value1, [case2, value2, …], [default])Arguments:
expression: Any valid value or reference to a cell or range.case1: The first case to check againstexpression.value1: The corresponding value to return ifcase1matchesexpression.- Other cases and values: Optional
default: The default value to replace #N/A if none of the cases match.
Basic Examples
The following formula returns “Off” if A1 is Sunday, “Half-day” if Saturday, and #N/A for all other cases:
=SWITCH(A1, "Sunday", "Off", "Saturday", "Half-day")To replace #N/A with “Working” for all other cases:
=SWITCH(A1, "Sunday", "Off", "Saturday", "Half-day", "Working")To handle cases where A1 might be blank and return “Working” instead of null:
=SWITCH(A1, "", ,"Sunday", "Off", "Saturday", "Half-day", "Working")Using a range in the expression argument without ARRAYFORMULA:
=SWITCH(A1:A, "", ,"Sunday", "Off", "Saturday", "Half-day", "Working")
This formula returns blank if column A is blank, “Off” if it contains Sunday, “Half-day” if it contains Saturday, and “Working” for all other cases.
SWITCH vs. Alternatives: IFS and IF
The SWITCH function typically evaluates expressions that result in numbers, boolean values, characters, or strings.
On the other hand, the IFS function can handle a wider range of data types, including booleans, numbers, strings, and comparisons between them.
For example, you can replace our SWITCH formula for the range A1:A with the following IFS formula:
=ArrayFormula(IFS(A1:A="Sunday", "Off", A1:A="Saturday", "Half-day", A1:A="", ,TRUE, "Working"))IFS requires ARRAYFORMULA support when conditions involve array references.
Syntax:
IFS(condition1, value1, [condition2, …], [value2, …])In this IFS function example:
- It checks each condition (A1:A = “Sunday”, A1:A = “Saturday”, etc.) sequentially.
- The value returned depends on the first condition that evaluates to true.
- If none of the conditions match (TRUE is always true), it returns “Working”.
IFS uses logical tests (e.g., A1 >= 100, B1 = “Yes”) to determine which condition is true.
Similar to IFS, the IF function can also handle a wider range of data types and comparisons between them. It uses logical tests (e.g., A1 >= 100, B1 = “Yes”) to determine which condition is true.
Syntax:
IF(logical_expression, value_if_true, value_if_false)The comparison should be between SWITCH and nested IF, as IF can only return either the value_if_true or value_if_false argument based on a logical_expression.
Here is how you can replace our SWITCH formula using nested IF to test A1:A against cases:
=ArrayFormula(IF(A1:A="Sunday", "Off", IF(A1:A="Saturday", "Half-day", IF(A1:A="", , "Working"))))When you specify a range in the logical_expression, you must enter the formula as an array formula.
Key Considerations
The SWITCH function is not case-sensitive and performs exact matching with cases. Therefore, it does not support comparisons such as ‘>50’.
For example, you cannot use it as =SWITCH(A1, “>50”, 1, “>60”, 2, “No Match”) because it does not evaluate conditions sequentially. It simply checks for an exact match and stops there.
Wrap-up
SWITCH: Use SWITCH when you need to test a value against exact matches of several cases and return the first matching value.
IFS: Use IFS when you need to test a value in a sequence of conditions and return the first matching value. You can include comparison operators for tests.
IF: Similar to IFS, but limited to one condition unless nested.
While both IFS and IF can handle comparisons, IFS can handle multiple conditions in a single formula, potentially reducing complexity compared to nested IF statements.
Google Sheets use a semicolon to separate arguments in formulas.
Hi, Anna,
It depends on your Locale settings.
Usually, a comma is used to separate arguments in formulas in countries where a period is used to indicate the decimal place.
I’m trying to get a huge list of items to switch. I’ve managed to use the Switch function to make the switches, but if a new item is added in, I have to add the cell locations into the formula for each new item. Is there a way to get the Switch formula to switch out ranges?
So, what I want is
=IFNA(Switch(Entry!B7:B,Engine!A7:A,Engine!C7:C))but that results in nothing being populated. It also doesn’t give any errors back.Hi, Zuzy,
That formula syntax is wrong. Please see the SWITCH syntax.
SWITCH(expression, case1, value1, [case2_or_default, …], [value2, …])I couldn’t see the ‘cases’ argument in your formula. Can I see what you are trying to do in a sample of your original file?
It’s an order form, and I would like the form to do the calculations for us.
So when a customer enters the material name on one sheet, it automatically switches the material name to the material price on another sheet.
But using Case1/Value1 means that every time we use a new material by adding a line, I have to update the formula.
This list of materials is just a small portion. There are over 200 options to choose from and we are always adding specifics like “green and gold” to the wood types.
And of course, customers never choose the same color option as the person before them.
Row # | Column A | Column B
0 | Material Names | Material Price
1 | Red | 20
2 | Yellow | 20
3 | Green | 20
4 | Black | 20
5 | Gray | 20
6 | White | 20
7 | Natural | 40
8 | Orange | 30
9 | Toxic | 30
10 | Forest Green | 30
11 | Sea Blue | 30
12 | Blue | 30
So, in my desired formula, Case1/Value1 would be comparing Column A to Column B and switching them out, without having to list them as A1, B1, A2, B2, A3, B3, and so on for 200+ times.
Hi, Suzy,
You are after a wrong function. Here you should probably want to use the Vlookup.
Assume the above table is in the sheet Sheet1!A1:B. The criteria (customer entry of material name) are in Sheet2!A1:A.
Then in Sheet2!B1, you can insert this formula.
=ArrayFormula(IFNA(vlookup(A:A,Sheet1!A1:B,2,0)))See if this helps?
If not, if possible, share the sheet with me.
… Link Removed by Admin …
Please keep this link unpublished. There is information that should not go out to the public.
The sheets I’m working through are the “Engine”, “Entry”, and “priceengine”.
Hi, Suzy Johnson,
Replace the formula in your Sheet with this one.
=ArrayFormula(IFNA(vlookup(B2:B,{Engine!G2:G,Engine!A2:A},2,0)))(in priceengine!A2)
If that doesn’t help, please make your Sheet’s sharing settings from “Comment Only” to “Edit” and share again. Also, give more details about the problem in the sheet itself.
I got it to work using that formula! I just had to pick out the correct columns on the correct sheets.
Can you use FILTER() as the value of a true condition? If so how? I’ve been trying, but it always comes up with an error.
So, I have a cell in the “Totals” tab with a dropdown option. Based on what is selected in that cell, I would like information from another tab to be filtered. This is the basic SWITCH() that I have made, but it doesn’t work. The filter does work on its own.
=Switch('Totals'!F1='Mesa', filter(Mesa!A:D, Mesa!A:A >= Totals!B1, Mesa!A:A <= Totals!D1),"Select an Option")Hi, Jeff,
You may want to try;
=CHOOSE(if(Totals!F1="Mesa",1,2), filter(Mesa!A:D, Mesa!A:A >= Totals!B1, Mesa!A:A <= Totals!D1),"Select an Option")Best,
Because IFS returns an error if none of the conditions are met; you can wrap IFERROR, around IFS, to create a default return value.
IFERROR(IFS(condition1, value1, [condition2, value2, …]),[default value])
Hi,
Welcome to Info Inspired!
What you said is correct. Without using IFERROR also we can address the issue. Please see this post.
How to Return Value When The Logical Expression is FALSE in IFS
Thanks