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

[@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.

[@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 😉

[@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!

[@hendersonliu423]

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

[@MyOnlineTrainingHub]

Glad you enjoyed it :-)

[@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!

[@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?

[@wayneedmondson1065]

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

[@MyOnlineTrainingHub]

Thanks so much, Wayne!

[@abdelkrimmesaiahmed662]

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

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@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!

[@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!

[@roberth.9558]

Thank you for your perspective on the switch function.

[@MyOnlineTrainingHub]

My pleasure!

[@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!

[@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.

[@MichaelBrown-lw9kz]

Another excellent video, and I love how you used the CHOOSE function to assign quarters to dates.

[@MyOnlineTrainingHub]

Thanks so much!

[@GainaIytics]

You're still the best Mynda. 🙂

[@MyOnlineTrainingHub]

Aw, thanks!

[@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 😊

[@clickbisi7016]

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

[@MyOnlineTrainingHub]

👍 cheers!

[@IvanCortinas_ES]

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

[@MyOnlineTrainingHub]

Cheers, Ivan 😊

[@chrism9037]

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

[@MyOnlineTrainingHub]

Great to hear, Chris!

[@19761999]

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

[@MyOnlineTrainingHub]

Thanks so much 😊

[@johnborg5419]

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

[@MyOnlineTrainingHub]

Cheers, John!

[@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!

[@vacilando86]

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

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Cheers, Darryl 😊

[@shoaibrehman9988]

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

[@MyOnlineTrainingHub]

Glad it was helpful, Shoaib!

[@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! 🙏

[@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.

[@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]

😁

[@techwg]

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

[@MyOnlineTrainingHub]

😁 great to hear!

[@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

[@Toot.100]

every time you think you've caught on Excel, you find something new to learn

[@MyOnlineTrainingHub]

😁glad I could share something new!

[@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.

[@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

[@zaighamuddinfarooqui1705]

I appreciate your recommendations of comparatively easy methods.

[@MyOnlineTrainingHub]

Glad you like them!

[@wilsonizekor651]

Thanks but I have a concern on this. Why is the month of Oct showing as 2nd quarter instead of 4th quarter? Same as June showing as 4th quarter instead of 2nd quarter?

[@MyOnlineTrainingHub]

Because I am classifying the months into their fiscal periods. For me the financial year starts in July.

[@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 😊

[@timmytesla9655]

Great video. Thank you.

[@MyOnlineTrainingHub]

Glad you liked it!

[@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

[@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.

[@cristoumanzor3896]

Great tip. Thanks!🙂

[@MyOnlineTrainingHub]

Glad you liked it!

[@hazemali382]

great Function Great Mynda ♥

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@hamidsh4789]

Thanks

[@MyOnlineTrainingHub]

My pleasure :-)

[@realhamza85]

Great, you are awesome..

[@MyOnlineTrainingHub]

Thank you so much 😀

[@775shahrukh]

if (AND(first logic ,second logic))......can swtich do this....??

[@MyOnlineTrainingHub]

Yes, =SWITCH(AND(first logic, second logic)...

[@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!

[@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 🍻

[@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.

[@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!