Is the new SWITCH function worth using?

[stevieinselby]

One of my colleagues has come back from training all fired up and excited by the new =SWITCH function in Excel ... but I'm struggling to see what the fuss is about. From the help guides that I have found, it just seems to be a way of hard-coding a lookup table into a single formula. While that might take up less space on the spreadsheet, it strikes me that that is an excellent way of un-future-proofing your work by making it more difficult to change the values at a later date if needed.

Have I missed something about SWITCH? Are there use-cases where it does add value?

 

[Special-K99]

I've not got 2016 but yep I agree.
And all it's telling you is if it is in the list or not and not how far down that list is.
Doesn't even look like you can replace the list of values with a range.

Similar to CHOOSE
or INDEX(...MATCH(...))

Maybe others have better opinions on this.

To be honest here we are three years on and this is te first I've heard about it.
Haven't seen anyone use it in any forum problems on the net.

 

[NiMip]

Came here looking for an answer to this exact question haha. I can't see any advantage if your test-values and result-values are already in a table, especially since you can't select test- or result-values as ranges. The only little thing I like is that SWITCH allows you to specify a result if a test-value isn't found, which would normally require an IFERROR for VLOOKUP or INDEX-MATCH.

I guess SWITCH is best if your test-values or result-values are scattered all around your worksheet? But to me that's a pretty disorganised worksheet.

 

[joeu2004]

To answer an old question, I would be wary of using the SWITCH function without doing some testing first.

(I do not have access to Office 365 -- I avoid like the plague that it is -- so I cannot test it myself.)

Recently, I learned that the IFS function evaluates all of its arguments, even if a left-to-right evaluation might find an early true condition and obviate the need to evaluate any further.

Of course, that is antithetical to why we often use nested IF functions: to avoid needless evaluations that might be costly in processing time.

Arguably, at least MSFT engineers had the sense to defer any error processing until-and-unless the left-to-right evaluation does not find an earlier true condition.

Nevertheless, the evaluate-all approach can cost a great deal of processing time if the needless evaluations involve expensive operations; for example, a needless VLOOKUP(...,FALSE) of 1+ million rows of data.

I would worry that SWITCH does the same thing.

Of course, that could be benign if the arguments are simple constants, like the examples on the support page. But it might be tempting to include expensive operations in some arguments.

FYI, the CHOOSE function works like the IF function: it only evaluates the argument that is selected by the value of the first argument.

The difference between CHOOSE and SWITCH is: CHOOSE requires a matching argument for all values 1 to n, whereas SWITCH can match arguments based on discontinuous numbers and even unrelated and different types of values.

There is no question that SWITCH can be very versatile. I just worry about its possibly poor implementation, like IFS. TBD.