HomeGoogle DocsSpreadsheetGoogle Sheets SWITCH Formula How to and Compare It with IF and...

Google Sheets SWITCH Formula How to and Compare It with IF and IFS

Published on

Learn the use of Google Sheets SWITCH formula. Once learned, you can use this logical function to easily replace several IF (nested IF) or IFS functions. I have detailed how to use the SWITCH function below and also compared it with IF, IFS and nested IF.

I am in the opinion that most of us are obsessed with IF and IFS. So, I think it’s time to limit the use of these two functions.

Show your brilliance in choosing a correct logical function for your need. It can not only improve your spreadsheet performance but also 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.

Regarding the logical functions, it’s always my best companion. I normally use the IF logical function as it can easily accommodate OR, AND logical functions with it. Also, the nested IF can meet your almost all logical test requirements in Google Spreadsheets.

But there is a problem with the nested IF formula. The nested IF is a combination of multiple IF formulas. This combination can make it tough to follow at a future date. At the same time, IFS and SWITCH are pretty easy to follow.

Though you cannot replace IF with IFS or SWITCH to solve your problems, on certain occasions the SWITCH formula is far better than IF, IFS or even nested IF.

Note: I have mentioned many functions below other than SWITCH. If you are not yet learned any of them, simply SWITCH to this function guide.

How to Use SWITCH Function in Google Sheets

Syntax of Google Sheets SWITCH Function:

SWITCH(EXPRESSION, CASE1, VALUE1, [CASE2, VALUE2, ...], [DEFAULT])
  • Expression – Any valid values. Example A1, or 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.

Basic Formula Example to Google Sheets SWITCH Function

Here is one simple example of the use of the SWITCH function in Google Sheets. After that, you can see on ‘complex’ use of this function.

Assume I have the value “P” in cell A1.

=SWITCH(A1, "P", "Present", "A", "Absent")

The above formula in cell B1 would return the string “Present”. Replace the value “P” in cell A1 with “A”, the same formula would return the string “Absent”.

Basic use of the SWITCH function in Sheets

In this example, I have used the value in a single cell as the “Expression”. But in the below example, I am going to use multiple values in a range as the SWITCH formula expression.

Google Sheets SWITCH Formula in Real-life Use

Here is one example of the real-life use of the function SWITCH in Google Sheets. Hope you will find this very easy to understand with the color pattern.

Google Sheets Switch Formula Example

Please carefully go through the formula and compare it with the SWITCH function Syntax. I’ve marked all the syntax parameters on the image.

Also please note that the above Google Sheets SWITCH formula acts like an array formula as it expands the result.

Now I think, I can move to the comparison of SWITCH with IF and IFS. Here is that much-awaited SWITCH vs. IF, SWITCH vs. IFS, SWITCH vs. Nested IF comparisons.

SWITCH Formula Comparison with IF and IFS

As I have already said, the SWITCH function is one of the less explored logical function in Google Spreadsheets. Let me try to provide you a few of its pros and cons in comparison with IF and IFS.

IF, IFS and SWITCH – Which One to Choose?

First of all, you should properly understand the purpose of these three functions. Then you can easily get an idea, which one suits your needs.

The functions IF and IFS can’t be used for the 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, IF and IFS arguments

Logical Expression, Expression, and Condition – quite confusing right?

What is Expression (SWITCH) in Google Sheets?

A cell or cells (array) in Google Sheets may contain an expression or data.

Data is the value you entered into a cell such as text, numbers, etc. But the expression is a statement or formula which returns a value.

Example:

Filter(A1:A,A1:A="US")

An expression can also be a reference to any valid value or values in a cell or range of cells.

=A2

Hope you could understand what is an ‘expression’ in Sheets.

What is Logical Expression (IF) in Google Sheets?

A logical expression can be 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.

Example:

=B2=1050

What is Condition (IFS) in Google Sheets?

The condition aka criterion can be a Boolean (also called Logical Value), a number, an array, or a reference to any of those.

Example:

Here you can consider the just above example formula which returns the Boolean FALSE.

I am going to one example where I’ve applied all the above three logical functions. You can easily understand the concept of expression, logical expression, and condition from this.

What is Expression, Logical Expression and Condition in Google Sheets

In this example, for the first formula using SWITCH function; A2:A8 is the expression. It’s a reference to the values in a range of cells.

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 the IF function, A2=”Sunday” is the logical expression or logical value which returns TRUE or FALSE.

That means the condition and logical expression are almost the same. Now to let us examine the formula results.

Differences in Switch, IF, and IFS Formula Results

In the above example, the SWITCH function has an edge over IF and IFS. The SWITCH formula tests an expression against a list of cases.

Switch Formula Explanation

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.

SWITCH Tests an expression against a list of cases

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 downwards like an ARRAYFORMULA.

IFS Formula Explanation

The IFS function evaluates multiple conditions or logical tests like A2=”Sunday”, A2=”Saturday” and returns a value that is corresponding to the first True condition.

IFS evaluates multiple conditions or logical tests

The problem here is, if no conditions are met, it can’t immediately return a False value (there is a workaround to this issue – How to Return Value When The Logical Expression is FALSE in IFS). 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.

IF Formula Explanation

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 the value_if_false part.

IF similar to SWITCH function use

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 formulas.

Replacing SWITCH formula with IFS Array Formula

Let’s begin with IFS. Here, a combination of ARRAYFORMULA and OR logical function can match the result of the SWITCH function. Below is the modified IFS formula which can expand its result similar to SWITCH.

=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 smarter. Anybody can easily follow the SWITCH function in a future date as it’s simple to read. IFS became much complicated to understand.

Replacing SWITCH formula with IF Array Formula

Here you are helpless with IF! You should use nested IF (multiple IF formulas) and ARRAYFORMULA combination to solve the issue.

=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 makes the formula complicated.

Why Users Reluctant to Use the Smarter SWITCH Function in Sheets

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 is 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 at all?

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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

13 COMMENTS

  1. 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.

  2. 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")

  3. 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])

LEAVE A REPLY

Please enter your comment!
Please enter your name here