Excel SWITCH Function, is it really as good as they say? You might be surprised!

The Excel SWITCH Function is an alternative to nested IFs, IFS, V or XLOOKUP by allowing you to replace one value with another, but does it really offer anything new or improved? In this video we compare SWITCH to IFS, VLOOKUP, XLOOKUP and CHOOSE.
Download the Excel file here: www.myonlinetraininghub.com/e...
0:19 SWITCH for Lookups
1:23 IFS for lookups
2:32 VLOOKUP & XLOOKUP alternatives
3:37 IFS for multiple logical tests
4:44 SWITCH for multiple logical tests
5:47 SWITCH to return Fiscal Quarters
6:39 CHOOSE to return Fiscal Quarters
7:17 Options reviewed
7:48 The bottom line!
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy

[@vijayarjunwadkar]

Thank you Mynda! It became clear as to, if we should "SWITCH" to this new function or "CHOOSE" the existing ones! Nice presentation as usual! BTW, liked the TRUE parameter logic in SWITCH! 😊👍

[@MyOnlineTrainingHub]

:-) cheers, Vijay!

[@wayneedmondson1065]

Hi Mynda. Very thorough and well explained! Thanks for the compare/contrast of these useful functions :)) Thumbs up!!

[@MyOnlineTrainingHub]

Thanks so much, Wayne!

[@Ado-7]

Keep up the great work. Best channel for project management. Thank you for the hard work and free tutorials.

[@MyOnlineTrainingHub]

Thanks so much, Adonis!

[@aleksandar40666]

its very useful to have this analysis (compare), to show quickly if its useful for daily business or not. Thx alot to you Mynda to save us time. which is today very precious.

[@MyOnlineTrainingHub]

Glad you like it!

[@manojjohnaxelsson2856]

You crack everything wide open and make it look so easy.... 👌

[@MyOnlineTrainingHub]

Everything’s easy when you know how. Hopefully you know how now 😉

[@IvanCortinas_ES]

Excellent Mynda!!! Great tutorial. Thanks for the detailed description.

[@MyOnlineTrainingHub]

Cheers, Ivan 😊

[@abdelkrimmesaiahmed662]

The simplicity ease the comlexity thamk you Mynda for this amazing video

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@chrism9037]

Fantastic! I have never used it and learned something new, thanks Mynda

[@MyOnlineTrainingHub]

Great to hear, Chris!

[@hendersonliu423]

I agree. I prefer sticking with vlookup but good to know this formula exists! Awesome video Mynda!!!

[@MyOnlineTrainingHub]

Glad you enjoyed it :-)

[@darrylmorgan]

Hi Mynda!Great Explanations of All These Functions...Thank You :)

[@MyOnlineTrainingHub]

Cheers, Darryl 😊

[@roberth.9558]

Thank you for your perspective on the switch function.

[@MyOnlineTrainingHub]

My pleasure!

[@19761999]

Excellent tutorial, thank you so much. You're an awesome teacher.

[@MyOnlineTrainingHub]

Thanks so much 😊

[@teoxengineer]

Mylinda hi, This "TRUE" trick is so wonderful and I have not ever seen it anywhere! Thank for sharing

[@MyOnlineTrainingHub]

Glad you liked it, Emre!

[@garys2187]

Thank you for helping me get better at Excel !!

[@MyOnlineTrainingHub]

You are most welcome, Gary!

[@paullowe4230]

Thank you, Mynda. That was a great explanation and really helpful.

[@MyOnlineTrainingHub]

You are most welcome! 🙏

[@clickbisi7016]

🙋🏾‍♂️One of the people who mentioned Switch in the IFS video here… I gotta say… touché 😆 Great tutorial as always 👍🏾👌🏾

[@MyOnlineTrainingHub]

👍 cheers!

[@alec1115]

I didn't know you can put a TRUE at the end of an IFS statement as an ELSE function. THANK YOU VERY MUCH!

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@cristoumanzor3896]

Great tip. Thanks!🙂

[@MyOnlineTrainingHub]

Glad you liked it!

[@emrc9777]

Awesome., love the comparisons… gonna check excel 365 to see if switch is there, I hope so!

[@MyOnlineTrainingHub]

Thank you! If you have 365 then you'll have SWITCH.

[@timmytesla9655]

Great video. Thank you.

[@MyOnlineTrainingHub]

Glad you liked it!

[@shoaibrehman9988]

Really appreciate your ideas how can we use according to different conditions. Thanks

[@MyOnlineTrainingHub]

Glad it was helpful, Shoaib!

[@EricHartwigExcelConsulting]

Great video! Thank you so much for sharing/creating this! I had no idea how useful SWITCH could be!

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@vacilando86]

The best thing with this formula is that you can use it like a let function which you can assign expressions

[@joshuabiondo9116]

I use the SWITCH function to SUM different columns depending on a dropdown, or even look at a different table entirely depending on a dropdown's value. Very fast function in my use cases so far. I love it!

[@MyOnlineTrainingHub]

Great use case.

[@helenasvensson6595]

Could you write an example of code here?

[@hazemali382]

great Function Great Mynda ♥

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@ABG_7]

You're still the best Mynda. 🙂

[@MyOnlineTrainingHub]

Aw, thanks!

[@marcelmaes5275]

7:50 Nice you pointed that out! I was wondering all the time why Microsoft has added this function anyway. Well at least it's easier then nested IFs 🙂.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@johnborg5419]

Thanks Mynda. I never use IFS OR Switch to be honest. Lookup functions are more convenient for me. : )

[@MyOnlineTrainingHub]

Cheers, John!

[@techwg]

You make my coffee mug at work more true all the time. It is a "I LOVE SPREADSHEETS" mug.

[@MyOnlineTrainingHub]

😁 great to hear!

[@hamzalokhandwala5101]

Great, you are awesome..

[@MyOnlineTrainingHub]

Thank you so much 😀

[@hamidsh4789]

Thanks

[@MyOnlineTrainingHub]

My pleasure :-)

[@dameanvil]

- [0:02] 📊 The SWITCH function in Excel 2019 is suggested as an alternative to the IFS function, offering different capabilities. - [0:22] 🔄 In the SWITCH function, the first argument is the expression, which can be a cell reference, value, formula, or Boolean value. - [2:33] 📝 SWITCH can be compared to VLOOKUP and XLOOKUP for lookup tasks, but may require more maintenance as values are hardcoded. - [3:39] 🎯 SWITCH can handle multiple logical tests, but it might be less intuitive compared to IFS. - [5:49] 🗓 SWITCH can classify dates into fiscal quarters, but the CHOOSE function might offer a more concise solution. - [7:59] 🛠 While SWITCH doesn't introduce new functionality to Excel, it's useful for those familiar with it from other programming languages.

[@jawedneshat5147]

Hi there. I get the point that it is not replacing currently available functions. However, I would like to know Switch is better in any circumstances other than the fact that it is very handy in DAX.

[@MyOnlineTrainingHub]

Yes, in DAX you don’t have lookup functions, but you can use related tables which is probably more efficient.

[@jawedneshat5147]

@@MyOnlineTrainingHub Does that therefore mean that the Switch function is adding no value when it comes to its usefulness in Excel?

[@MyOnlineTrainingHub]

IMO, yes.

[@tmb8807]

Using TRUE as the expression is diabolical genius. IFS is more intuitive but I like being odd (as long as there’s no performance hit)!

[@MyOnlineTrainingHub]

😁glad you liked it!

[@zaighamuddinfarooqui1705]

I appreciate your recommendations of comparatively easy methods.

[@MyOnlineTrainingHub]

Glad you like them!

[@ParthPatel-dr6up]

Hi, I want to ask a question on which function should i use for the below scenario: I have fraction data between 0 to 8 in 5 columns. I want to assign a value on basis of range. Let say number = N. If N

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@allajagadishkumar8661]

How to select Cell range in "Value" and also for "Result" ,if I required to get data which was already present in a Normal Range using Switch. Can we do that....just like sumif, can we select a range in switch or we need to enter Manually Everything..

[@MyOnlineTrainingHub]

Not sure what you mean, Alla. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@ennykraft]

I use the SWITCH function combined with EOMONTH to return the last day of the quater for any given date. In this case SWITCH makes sense. It's only 12 values and they will never change. But most of the time a separate lookup table makes more sense.

[@MyOnlineTrainingHub]

Thanks for sharing your idea, Enny!

[@richardhay645]

Thank you. I've never found a good use for SWITCH. Now I understand why! There is no good use in Excel!!!

[@MyOnlineTrainingHub]

😁

[@saberconstruction7224]

I am very green at this excel. thing. I've only used it in a very elementary way. That being said this question is going to sound dumb. In your example of the dates. How does excel know you're referencing the 4 quarters of ayear?

[@MyOnlineTrainingHub]

It's a mapping of the month number to the respective quarter number. It's simply returning a number that we interpret as a quarter: the formula looks up the month number in column B and returns the corresponding quarter number as defined in the formula. e.g. month 1, returns number 3, which we interpret as quarter 3 and so on. HTH.

[@yuppymike]

16 years I’ve been using Excel and I had never heard of Switch or Ifs. Yet I’ve used Switch every day in Swift. Unbelievable.

[@MyOnlineTrainingHub]

Glad I could share something new with you, Michael 😊

[@caty863]

Actually the "SWITCH" statement is very new in Excel. You won't find it in versions before Excel 2016 for instance. Same is true for Dynamic Array Formulas which only appeared in Excel 2021.

[@Droolster]

To be fair, SWITCH is now in 2019/365

[@CozmoNz]

Switch looks like it was simply ported over from the programming language R - Bit overkill for excel since it's not being procedurally (in this case at least)

[@MyOnlineTrainingHub]

Agree, Matt.

[@bzflowerbee]

How do I apply the * to switch function? Let's say I have a list of numbers and I want to look up the numbers with first 3 digits 222 return to "good ". Thank you 😊

[@denizaksen2972]

Maybe by using the *LEFT(*_cell_address_*, 3)* inside *SWITCH()* it could work out. But then you have to compare it to *"222"* and not just the value *222.* If a value comparison is needed, then you may enclose *LEFT(*_cell_address_*, 3)* expression inside the *VALUE()* function.

[@MyOnlineTrainingHub]

I wouldn't use SWITCH for this. It's cleaner to just use IF similarly to how Deniz suggested, e.g.: =IF(LEFT(TEXT(A2,"@"),3)="222","Good","")

[@bzflowerbee]

@@MyOnlineTrainingHub Thank you very much! I works perfectly.

[@josephcoon5809]

Have you ever considered creating a Concat() cell that accepts list information and formats it into an equation that you can just copy/paste into another cell? For instance a range of cells contains your city/country information, and a cell concats all threat information with the proper punctuation for a an expression. Now you can dynamically change the contents of an expression or create new ones without typing everything out like you did at 1:10 . Additionally, it helps for trouble shooting or modification for other expressions.

[@MyOnlineTrainingHub]

Thanks for sharing, Joseph!

[@josephcoon5809]

@@MyOnlineTrainingHub Least I can do for all that you’ve shown me so far. I’m still picking through your dashboard video a little at a time. One Love, Cheers 🍻

[@dudescott8983]

Nor really the purpose of the video, but you can use a simple(?) formula for the date to quarter conversion rather than switch or choose. =LET(qtr,ROUNDUP(MONTH(C7)/3,0),qtr+IF(qtr

[@MyOnlineTrainingHub]

Thanks for sharing, Scott!

[@bamakaze]

Not too many reasons to ever use switch.

[@MyOnlineTrainingHub]

Nope :-)

[@caty863]

People like you want me to believe that we don't actually need the "SWITCH' statement in Python. No, actually we do. People who say we don't simply don't know better!

[@MyOnlineTrainingHub]

I'm not speaking for Python. You do what you know is best there. I'm only speaking for Excel.

[@caty863]

@@MyOnlineTrainingHub I didn't say "you" specifically, I said "people like you". I mean, it's not a mark of brilliance to try to cover the shortcomings of a tool by selling these as a good thing. A bug is never a feature.

[@alializadeh8195]

Thanks

[@MyOnlineTrainingHub]

My pleasure, Ali!