1

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?

  • yes it is if you input range instead of cell reference – MARK MY ANSWER Jan 27 at 17:45
  • 1
    We don't have much to go on, Annan. And I have a feeling your actual data isn't going to adapt well to whatever we can offer sight unseen. That said, my gut is that I'd go with a =ArrayFormula(VLOOKUP(CELL_REFERENCE,QUERY({values, result1, result2}),{2,3},FALSE)) setup. – Erik Tyler Jan 28 at 6:14
0

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:

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.