SWITCH Function in Google Sheets: A Comparison with IF and IFS

Published on

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 against expression.
  • value1: The corresponding value to return if case1 matches expression.
  • 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")
Examples demonstrating the use of the SWITCH function in Google Sheets

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.

Prashanth KV
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.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.