Google-sheets – Is it possible to return a range from an IFS or SWITCH

google sheetsgoogle-sheets-arrayformulaworksheet-function

I am trying to simplify some 'if else if else' code by replacing it with a single IFS or SWITCH expressions. I have simplified all the code to be trivial but show the problem.

This code works and two cell values are returned:

= IF(CELL_REFERENCE = 1,
    {
        "A";"B"
    },
    IF(CELL_REFERENCE = 2,
        {
            "C"; "D"
        },
        {
            "E"; "F"
        }
    )
)

This code does not work with only the first value being returned each time:

= IFS(CELL_REFERENCE = 1,
    {
        "A";"B"
    },
    CELL_REFERENCE = 2,
    {
        "C"; "D"
    }
    CELL_REFERENCE = 3,
    {
        "E"; "F"
    }
)

The same issues happen when using SWITCH, with only the first value of a range being returned.

Is there a good solution for these cases, or just keep using multiple IF expressions?

Best Answer

You may think that behaviour of IFS() is similar to IF() and reason of having IFS() is to avoid nesting of multiple IF() but that's not so true. Yes, there are some common baselines however, there is a major difference when it comes to arrays. IFS() in a combination of arrayed output expects arrayed input - that's why you got returned single-cell output instead of arrayed output.

  • let's say your IF() formula is like:

  • "converting" it into IFS() will return this (because cell A1 is not array/range):

  • now let's add a arrayformula (ranged input):

  • also note this behaviour:

Related Topic