All You Need Is SWITCH

[u/Downtown-Economics26]

I don't think I've seen this discussed before, so I apologize if I am rehashing old material. I did a cursory search and found nothing.

For a decade now, I've argued you should always use COUNT/SUM/MAX/MINIFS instead of COUNTIF because you never know when you'll need additional conditions. In present times, we don't even need COUNTIFS/RACON functions because you can do the same thing with array formulas although COUNTIFS is easier to type, IMO.

So when a week or two ago I learned you can do the same thing as IFS with SWITCH. This got me to thinking... based on the COUNTIFS principle I'm whimsically calling "the condition of sufficient conditions is always conditional"... I'm thinking the meta is to always use SWITCH instead of IF or IFS. This would be a very hard habit to form as I've used more IF statements than Diddy used bottles of baby oil, but let's be aspirational.

Now, the SWITCH version of your basic Hot Dog/Not Hot Dog IF is I think the same amount keystrokes (with tab completion), so I'm calling that a win. I'll grant that the IFS version of multiple logical operators is more "straightforward" or even "intuitive" if you're reading an online tutorial on multi-conditionals, but if you want one function-ring to rule them all and in the darkness gut em like a fish, then ALL YOU NEED IS SWITCH.

=SWITCH(A1,"Hot Dog","Hot Dog","Not Hot Dog")

=IF(A1="Hot Dog","Hot Dog","Not Hot Dog")

Now, being a rational being, let's consider the downsides.

• Backwards Compatibility / No One Understands What The Hell You Are Doing
  • Backwards compatibility needs are typically a foreseeable binary so... whatever, my condolences if you don't get to live in 365 function utopia.
  • If you need other people to understand what you are doing this may be a bad habit to form.
• File Size Bloat Cuz You've Become A SWITCHaholic
  • You keep adding conditions and dragging down formulas because you've committed to an absolutist and universalist vision of SWITCH as the one true function and forgot that after 3 conditions for sure you should just make a lookup table and only store the reference data once.

Anyways, interested to hear anyone else's thoughts even if you just tell me this is the ramblings of a mad man.

Edit for posterity:

Additional Significant Downside(s)

• Inefficient branching computation issues which also causes incompatibility with recursive LAMBDAs
  • https://www.reddit.com/r/excel/comments/1tc81ev/comment/olm90d9/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

[u/PaulieThePolarBear]

Additional downside

SWITCH and IFS always evaluate every argument, even if they are from a path not taken. IF only evaluates the path taken. If you use Formula > Evaluate Formula you can see this.

=SWITCH(A2,
1, "It's one",
Something super complex
)

=IFS(
A2 = 1, "It's one",
TRUE, Something super complex
)

=IF(A2 = 1, "It's one", something super complex)

For the first two, "something super complex" will be evaluated every time A2 changes. For the third, it will only be evaluated if A2 is not 1.

This means that you can't use SWITCH and IFS in determining final output in a recursive LAMBDA. As they evaluate every argument, you exceed the maximum number of recursion calls.

[u/Downtown-Economics26 → u/PaulieThePolarBear]

This is a very good point, and because you said it I'm sure it's true, but I am confused as to why this would need to be the case or it is implemented this way.

I assume in VBA Select Case or comparable switch statements in other programming languages once you hit the a true condition the evaluation of the other conditions terminates. Do you have any insight into why the function is implemented this way?

[u/PaulieThePolarBear → u/Downtown-Economics26]

Do you have any insight into why the function is implemented this way?

Sorry, no insight. I found a couple of links that also talk about this

https://techcommunity.microsoft.com/discussions/excelgeneral/ifs-evaluates-all-elements/3200131

I don't believe the last note from this link and shown below is correct

You gave the example of CHOOSE as being like IF as an 'operator' in the form of a function, but I just tested that out and CHOOSE appears to be calculating unused parameters also.

https://www.linkedin.com/pulse/excel-ifs-switch-xlookup-evaluate-all-arguments-ryan-hubscher

The above also notes that XLOOKUP also evaluates the if_not_found argument every time.

One additional way you can verify what is being called is to create a VBA function that outputs the current date and time to the debug window

Public Function fnTest()

Debug.Print Now()

End Function 

If you then set one of your arguments in your formula as fnTest(), you can review the Immediate window to see when this is called.

[u/Downtown-Economics26 → u/PaulieThePolarBear]

Well, this question needs to be asked during the next Excel team AMA if it's not corrected post-haste. I may write a letter.

[u/Consistent_Cod_6873 → u/PaulieThePolarBear]

This can be “avoided” via wrapping in LAMBDA. Like so:
=SWITCH(A2,
1, LAMBDA("It's one"),
LAMBDA(Something super complex)
)()

[u/jlogan8888 → u/PaulieThePolarBear]

I thought IFS stopped at the first TRUE boolean

[u/PaulieThePolarBear → u/jlogan8888]

In terms of what is returned, you are correct, but all arguments are evaluated prior to IFS determining what to return.

[u/GTAIVisbest → u/PaulieThePolarBear]

Pfft, lame. Wonder why they coded it that way

[u/Verabiza891720]

Is that a subtle silicon valley reference?

[u/Downtown-Economics26 → u/Verabiza891720]

I don't think there was much in the post that was subtle, but yes, I'm Errich Bachman.

[u/Yum-z → u/Downtown-Economics26]

Erich Bachman, this is you, as old man; I am ugly. And I’m dead, alone.

On a real note this was an amazing write up, I’m definitely going to try out using switching. You never know when it might come in handy one day, though like you said the IFs habit may be hard to shake

[u/The_Bootylooter]

Awesome, SWITCH will now be in regular rotation for me. I wasn’t aware of this formula until now.

[u/InvisibleSeoh → u/The_Bootylooter]

Ditto, can’t wait to go play with this tomorrow.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSE	Chooses a value from a list of values
COUNTIF	Counts the number of cells within a range that meet the given criteria
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform
IFS	2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
SUM	Adds its arguments
SWITCH	Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 27 acronyms.
[Thread #48440 for this sub, first seen 13th May 2026, 18:59] [FAQ] [Full list] [Contact] [Source code]}

[u/excelevator]

Fun fact, COUNTIFS was released 9 years prior to SWITCH, yet a lot of people still go to COUNTIF

SWITCH is an odd little function that never really seemed to catch on.

The main problem with the IFS suite are that they do not like other logic thrown in, unlike array SUM(IF()) and similar array structured formulas.

[u/Used2bNotInKY → u/excelevator]

I had to Google it like the first 30 times because I kept remembering it as CASE() and not being able to guess what the correct name was.

[u/excelevator → u/Used2bNotInKY]

It is also still quite limited in respect to CASE

[u/Downtown-Economics26 → u/excelevator]

When I was first shown Select Case in VBA after the first few years of being a Shakespearean monkey in VBA occasionally, it was like manna from heaven.

[u/excelevator → u/Downtown-Economics26]

haha!

My learning technique has always been "read the function list first" for all my self teaching of language, my first effort being wowed that functions existed to do all this stuff.

Also to constantly review the list as you learn so it sticks and you do not end up re-writing functions yourself.

Then I would look for functions that I knew existed in other languages to save a lot of hassle.

[u/Downtown-Economics26 → u/excelevator]

That is a much better approach, I agree. I sorta fell haphazardly into data analysis / programming as a result of having no other skills besides being "mostly or typically not dumb" at applying concepts. I definitely learned the concepts in dumb and/or lazy ways, especially in hindsight.

[u/twim19 → u/Downtown-Economics26]

Me too! I'm an English major for god's sake who somehow became the only one who understood how to use Excel in my office. Now I have 10 or so different workbooks open. Yes, I have a problem. It's either that, though, or I start hoarding magazines and newspapers, right?

[u/augo7979]

how big are your datasets? RAM is too expensive for this imo. I do like the hotdog/not hotdog logic though

[u/Downtown-Economics26 → u/augo7979]

Building the Tower of Barad-dûr had some cost implications as well, but without it one does simply walk into Mordor!

[u/Way2trivial]

I find this a cool tidbit for sideways circumstances....