Excel Battle: CHOOSE vs SWITCH

[@BrendanCosgroveHere]

Never used either of them before! I'm going with SWITCH next time!

[@ExcelU]

Brendan!!!!!!!!!!!!!!! Thanks for your comment man, and glad you like SWITCH :)

[@lindaflorence5174]

Love, love, love Excel University training! Was unfamiliar with both Choose and Switch but now have those tools loaded in my arsenal. Thanks, Jeff!

[@ExcelU]

Thank you for your kind note :)

[@kevinmahernz]

Thank you for this, I wasn't familiar with either function but will give switch a go

[@therealjeffg]

Great video, thanks Jeff

[@mcguiganmike]

Switch it is! So glad I watched this video - nested IF statements are hell. Thanks!!!

[@EricHartwigExcelConsulting]

Really great example of both functions and how they can be used in different ways!

[@ExcelU]

Thanks ... glad it helped!

[@EricHartwigExcelConsulting]

@@ExcelU I am looking forward to using them more thanks to your video so it defiantly helped. 🙂

[@IssueBoyStefan]

Switch of course, most of the time combining it with True() as a first argument. ;)

[@mukeshbehera4499]

awesome. I would go with SWITCH one.

[@roberth.9558]

Well done. Thank you.

[@silaslee1333]

switch is a go, thanks!

[@gandhisunil3]

It is based on situation we have, sometimes we prefer choose, other times switch

[@ashleymoore1845]

Definitely will use SWITCH. Very much like the VBA function “Select Case”.

[@tobylmiller]

Great example of both, but I would default to Switch!

[@SteveHonakerII]

I have a particularly long statement in some complex sheets. Adding additional arguments has become quite difficult. I'm going to rewrite this to a switch. Solely for the sake of readability and expandability. I prefer to have a deep knowledge of a few functions to accomplish as much as I can. My new primary arsenal will now be: FILTER, LET, SWITCH, SEARCH, LEFT, RIGHT, CONCAT, IF

[@philiphinton1350]

Could I suggest TEXTJOIN rather than CONCAT, Steve? CONCAT does this: =CONCAT(B14:F14) returns "RepRegionSalesMarginSwitch" with no delimiters unless we do this =CONCAT(B14&", "&C14&", "&D14&", "&E14&", "&F14) TEXTJOIN does this: =TEXTJOIN(", ",,B14:F14) returns "Rep, Region, Sales, Margin, Switch"

[@GoWest011ned]

SWITCH for the more complex situations and also affords more flexibility. CHOOSE for simpler scenarios.

[@meteora8888]

Switch looks great 👍

[@ajayshivpuje3797]

I am going with SWITCH function 😊

[@micheltw]

With switch can you define array of multiple choice with curly brackets?

[@ExcelU]

Yes ... however the formula will spill in order to return one result for each choice. One option to solve for that is to wrap the CONCAT and IFERROR functions around SWITCH. So, it would be something like this =CONCAT(IFERROR(SWITCH(A1,{1,2,3}, ...),""))

[@RandyNevers]

Switch rules!

[@Mishkafofer]

XL file download don't work. Excellent video.

[@ExcelU]

Sorry you were unable to grab the download file. I just tested and it seemed to work ok for me here. Email us at [e-mail] and we'll email it no worries. Thanks Jeff

[@meniporat3527]

2 comments if I may: 1. you can "overcome" the #VALUE! error in the CHOOSE formula, if you wrap it with an IFERROR 2. Two additional advantages of SWITCH over CHOOSE 1) CHOOSE can accept only positive integers whereas SWITCH can accept any integer (positive, negative, zero) 2) SWITCH can accept a decimal number whereas CHOOSE rounds the number down.

[@ethanhunt6422]

switching to switch

[@ventjemazzel8822]

SWITCH 👍

[@muhammadsadiq2330]

earlier CHOOSE now SWITCH

[@christosmylonas]

switch!

[@romelkarouw8515]

I choose "Switch"

[@stevejez]

Another excellent video, thank you Jeff. I wonder who thought SWITCH was an appropriate name for this functionality. Surely it should be CHOOSES as in IF & IFS

[@ExcelU]

Thanks! The name for SWITCH probably comes from programming languages which offer the same function :)

[@johnkuczmanski6012]

I believe you use the switch function for IF, AND, OR statements.

[@NPC-W]

how about IFS vs SWITCH🧐

[@bodinian]

IFS works better in my opinion. If one function does not work, you can tell it to try a completely different function next. Switch only lets you use one function for everything listed.

[@ericzhangozhin8532]

SWITCH

[@fredkh9256]

Heisenberg

[@FrancisXavierD]

SWITCH is the winner

[@jayanththealphamale]

I'm such a loser for liking this. Can't help myself.

[@ibrahim82649]

Switch wins