Say Goodbye to IF Statements in Excel with SWITCH

Рет қаралды 416,310

Күн бұрын

This tutorial will show how to use the SWITCH function in Microsoft Excel. We start by exploring the basics of the IF function, a staple in Excel's formula arsenal, to ensure a solid foundation. Then, we introduce the SWITCH function - a lesser-known yet incredibly potent tool in Excel. We'll compare these two functions side-by-side, illustrating how SWITCH can provide cleaner, more readable formulas, especially in scenarios with multiple conditions.
Key highlights of this video include:
A comprehensive overview of IF and SWITCH functions.
Practical examples demonstrating the advantages of SWITCH over nested IF statements.
Step-by-step guidance on how to implement the SWITCH function in real-world scenarios.
Tips for enhancing formula efficiency and readability in your Excel projects.
By the end of this tutorial, you'll be well-equipped to make the switch (pun intended!) from cumbersome IF statements to the elegant simplicity of the SWITCH function. Whether you're managing data, analyzing reports, or love to streamline your Excel workflows, this video is your gateway to next-level Excel proficiency.
See why XLOOKUP is Superior to SWITCH: • Why XLOOKUP Reigns Sup...
Practice File: go.teachers.tech/SWITCH_FUNCTION
Timestamps:
0:00 Introduction
0:20 Replacing a Nested IF statement with SWITCH
3:21 Using an Operation with the SWITCH function
5:30 Using an IF statement with a nested SWITCH function

Пікірлер: 425

@Gorman-84 2 ай бұрын

I am the master of nested IF statements. You have ruined my life. Just kidding. This is great. I cannot wait to replace all of my nested IFs. Thank you.

@AlThePal78 Ай бұрын

you can use =IFS() as well so they don't have to be nested and it looks more like this switch statement

@NickCombs 19 күн бұрын

If statements are still needed in any situation where you test different data points. Cases where you have just one value to test are actually not as common as you'd think outside of simple demos.

@highestbuilding 3 ай бұрын

I didn't know about this function. But after watching your video, I still prefer using tables with the xlookup-function to keep things as variable as possible.

@jmacdono 3 ай бұрын

How can you use XLOOKUP to insert values based on criteria in other cells? SWITCH has a different function it seems to me.

@davescrams 3 ай бұрын

@@jmacdono You create a table with values that correspond to the translation. Consider the second example. You could create a separate table with a column for region and another column for the associated commission percentage. In the original table, you use xlookup to lookup the region and return the commission percentage. This approach is much easier to manage if the number of categories is large or likely to change.

@c17nav 3 ай бұрын

@@jmacdono Microsoft's web site help is a great resource for all of Excel's functions. Heaven help you should buy one of the many books available.

@jakubklima9002 3 ай бұрын

Exactly what I tought while watching the video.

@ramilaquino5350 3 ай бұрын

based on experience, i could say the same, using a table and xlookup is better as it is dynamic. works same way as a join in SQL, and it handles missing results much better.

@eng.halmatmuslih4714 3 ай бұрын

True “IF” is not handy in this situation, but you can use”IFS” function, same results and neat as much as switch. Thanks for the explanation, another function is added to the line, helpful.

@alexk7467 3 ай бұрын

That's true, but the only problem with "IFS" is you must have a value which is true. So if any value doesn't match any in the "IFS" statement the statement will fail.

@sql638 2 ай бұрын

@@alexk7467You can insert as last condition a ever-true one, so a condition without any restriction, simply ……;TRUE”;”Value you desire”). So if all the previous conditions fail, this one will apply

@agwosdz1981 2 ай бұрын

For that, you can encapsulate the function with IFNA or IFERROR :)@@alexk7467

@TheJaswald Ай бұрын

@@alexk7467 You can set the final test value to "TRUE()" then the result: =IFS(test1, result1, test2, result2, ..., TRUE(), result_n)

@yelnatsch517 Ай бұрын

@@alexk7467the very last statement in the IFS could just be 1 then set the default value. Example: ifs(case1, “A”, case2, “B”, case3, “C”, 1,””). If all cases fail, the default will be “”.

@jyotikumarrout7323 3 ай бұрын

Wow, this tutorial on using the SWITCH() function is incredibly helpful! I've always struggled with nested IF statements, but this alternative seems much more efficient and organized. Thanks for breaking it down step by step and providing clear examples. Can't wait to start implementing this in my own projects!

@nathanh9935 3 ай бұрын

I always did a vlookup function with a table to change a number to a rating. Also, I find that it is still pretty clean to use 2 digits concat in another table to change a double input into a single output. The formulas are clean that way too. Still, I'm glad to know the switch function exists.

@RichardJJennings 2 ай бұрын

At 1:30 I realised this is the DECODE function in SQL. Super handy and I no longer need to do those awful nested IFs. Thank you

@garciarogerio6327 Ай бұрын

I use SWITCH it in Power Bi. I normally use IFS to avoid nested if statement. Great one!

@troelstrc 3 ай бұрын

Thank you for this - I understand that this video is about the function switch. But for efficiency and future adjustments, I would put all the values into 1 or 2 tables and use the strengths of xlookup to return the desired results, instead of hardcoding it into a formula.

@TeachersTech 3 ай бұрын

I would agree that.

@Ian-gf8id 3 ай бұрын

@troelstrc Exactly. In this example a simple table of corresponding data (Performance Score / Performance Rating) can be used for multiple purposes including the source for Data Validation List (i.e. the Performance Rating cell range as a drop-down menu) and in one's preferred Lookup formula. Hard-coded data in formulae can be very restrictive, and make back-tracing errors and amendments very difficult. I avoid hard-coded data in formulae wherever possible.

@firozmakrani 3 ай бұрын

Using excel for decades but today I learned about switch. Thanks ❤

@RemofRenaissance 3 ай бұрын

Thanks for this, I like the structure of the formula (more so than “IF” as simpler for those nested functions as you’ve also highlighted). As others have said I prefer reference tables and lookups for most instances but I will definitely use the “SWITCH” formula as have many use cases like dynamic formulas to work out year to date values. Great to learn something new 👍🏾

@donniemcgee7523 3 ай бұрын

This is an extremely helpful tutorial. Thank you so much for sharing it. You did a great job of laying out and explaining the material. Thank you, again!

@Pattythomas5 2 ай бұрын

I have not heard of the switch function...THAT was excellent! Thank you!

@power-reconcile 15 күн бұрын

Well explained! Thanks for sharing!

@colinubeh1180 3 ай бұрын

Hey! this is simpler than nested if function. THANK YOU!

@johnbufton1487 29 күн бұрын

Excellent, I shall using it. Thanks!

@Akulion1 Ай бұрын

Wow what a time saver! Truly good by nested IFs lol thanks for sharing!!

@DB-nl9xw 3 ай бұрын

thank you for sharing the knowledge

@shadeburst 2 ай бұрын

Happy days! This is the honorable descendant of the CASE statement in good old FoxPro. Well done MS!

@terjidjurhuus1917 3 ай бұрын

I use Switch in PowerShell all the time, but funnily it never occurred to me that it exists in Excel. Really useful, thanks!

@infamous8541 3 ай бұрын

switch is also available in many other programming languages

@goodgollywally Ай бұрын

Thank you so much. I loved it.

@daonguyen521 3 ай бұрын

Very helpful teacher. This is the 1st time I know about this function

@robertsamagalsky1617 16 күн бұрын

Love the new SWITCH function!

@athar786100 3 ай бұрын

Great examples! I had never heard of switch.

@anacom4238 4 ай бұрын

You explain things very well and it's easy for me to learn from your videos despite my adhd. Thank you!

@TeachersTech 4 ай бұрын

You’re welcome!

@prabhakarrao4922 Ай бұрын

Brilliant - Thanks for sharing. Really appreciate your work. Kind Regards

@philipperiondel3093 3 ай бұрын

Actually, it is the equivalent of Select case in VBA. Thanks a lot, I did not know this function existed in Excel. It would have saved me a lot of time if I had known it sooner.

@tomcoleman8702 3 ай бұрын

Thank you KZfaq algo! I actually can use this Monday at work... completely useful and relevant. Very nice examples!

@TeachersTech 3 ай бұрын

Great to hear! Thanks!

@larrywill464 2 ай бұрын

Great function! Thanks a lot, I didn't know about this

@PlantBasedPietro 4 ай бұрын

Never heard of the Switch function. Thank you Jaime!!! :)

@TeachersTech 4 ай бұрын

You're welcome!

@goingvenus5603 3 ай бұрын

Very interesting and well-presented. Thanks!

@leovanhorrik9526 3 ай бұрын

Thank you very much. I didn't know about this formula but will definitely use it in the future.

@TeachersTech 3 ай бұрын

Glad it was helpful!

@warriorlink8612 Ай бұрын

Thanks for the demo. I can see some scenarios where this could help.

@scottgardner7318 3 ай бұрын

I have not seen this function. Brilliant! TY

@robertdufour2456 3 ай бұрын

Thank you for all of your help through the years!

@TeachersTech 3 ай бұрын

You're very welcome!

@wahas2752 3 ай бұрын

Thank You. This will make my sheets so less complicated now.

@sparacin 2 ай бұрын

Excellent. I already have a couple of work cases that can benefit from this SWITCH function!

@nerissawood73 5 күн бұрын

I had never heard of this function and will definetly use it from now on. I regularly miss brackets or put them in the wrong spot doing long clustered IF functions. And can see the ease of modifying a statement if you need to add more options later on. Tx for sharing!

@Pronellyllon 3 ай бұрын

Thank you for this video. While I am known as the Excel genius at my work, I wasn't aware of the Switch function. I am sure I'll be able to have a lot of fun with this function in the future

@rlsbdog 3 ай бұрын

It's easier, quicker and more dynamic to use vlookup (or xookup) or index/match to a reference table. For "No Rated", you can wrap your function in an IFERROR.

@Pronellyllon 3 ай бұрын

@@rlsbdog I don't think they have the exact same purpose. The lookups and index/match functions are referring to a singular cell. In one of my Excel-tables, I have a sumifs function in which I would want it to select another column based on the value of another cell. In this case, I think the switch function fits better.

@danguee1 25 күн бұрын

@@Pronellyllon switch function is inflexible and undynamic

@Pronellyllon 20 күн бұрын

@@danguee1 it worked pretty well for me. In a situation where I used the IFS-function and continuously referred to the same cell had a specific value then needed to pick up a specific column in a table, I used the SWITCH-function instead. Now I didn't need to refer to the same cell anymore. Everything else in that formula stayed the same

@leandratel2466 3 ай бұрын

SWITCH is nice, but I'm more a fan of IFS. Nice video ❤🙏🏽

@liquidapathy82 3 ай бұрын

Absolutely excellent info. I want to try it on some spreadsheets that have some complex nested if statements

@stuartbrown3859 Ай бұрын

Awesome Tutorial, so much easier.

@lawon01 Ай бұрын

This helped alot thank you!!!

@caiopjv 3 ай бұрын

Excelent and very useful! Thanks

@amystiksma1116 4 ай бұрын

This was amazingly useful! I have so many nested ifs in a budget spreadsheet, and it makes it so hard anytime I need to change things. Usong switch instead is going to make it so much easier! Thanks so much!

@TeachersTech 4 ай бұрын

You're so welcome!

@angelgeorge7629 3 ай бұрын

This is AWESOME!!!! Was able to simplify so so soooooo many IF statements in a spreadsheet I am working on.

@TeachersTech 3 ай бұрын

Glad it was helpful!

@albertokusmic9239 3 ай бұрын

great, did not know about it, thanks for sharing!

@apoorvasgala 3 ай бұрын

Hi ! Nice video. Very informative about Switch function. Honestly speaking I didn't knew about this until now. Thanks for sharing knowledge. I read many comments below about usability of this function and suggestions about instead use xlookup, index match and other various function. They may be right in their opinion as xlookup, index are very powerfull. BUT, this tutorial is about Switch function and not about finding alternatives to if function or switch function. See, when these powerfull functions were not available we used to use if function very largely. But now with advancements in MS Excel we get so many new functions with mind blowing capability. It may so happen that one day there would be better function available then xlookup! Who knows. So I would stick to commenting on Switch function and its usability, quality of the video, quality of content of video like language and graphics used, explanation done, example shown and discussed rather then speaking of alternative to switch function. Overall very interesting and informative video.

@fssacko 20 күн бұрын

Great thank you lot Mrs Teacher's Tech

@WiFiJeremy 2 ай бұрын

Pretty cool. I didn't know about this one.

@ARIES9327 Ай бұрын

ahhh saved so much time ! amazing!

@wv1138 2 ай бұрын

Never used switch. Liked and subscribed. Good input.

@ExcelTutorials1 3 ай бұрын

I love the SWITCH function!!! =)

@davethorpe5947 3 ай бұрын

Very useful! Thanks!

@silviopoggi8193 4 ай бұрын

So very helpful! Excellent presentation regarding a function I never heard of...

@TeachersTech 4 ай бұрын

Glad it was helpful!

@alexrosen8762 3 ай бұрын

Extremely well explained and perfect real life examples

@rchsuccesspartnersllc155 Ай бұрын

Nice- I didn't know... Thanks

@marklutz5837 4 ай бұрын

Recently retired, worked with Excel for years and didn't know about switch. Looks very handy, can think of many times I would have chosen that instead of the clumsy "if" statements. Thanks.

@TeachersTech 4 ай бұрын

I’m glad you found it useful!

@tomjones1502 3 ай бұрын

SWITCH is a new function not find in older version of excell

@anggaraw 2 ай бұрын

@@tomjones1502 This function only in 365 right?

@tomjones1502 2 ай бұрын

No, SWITCH function was added in year 2016, in excel 365

@tomjones1502 2 ай бұрын

Switch function is in Excel 2019 and 365 (not sure but 2021)

@HeroZaki_ 3 ай бұрын

I just heard about it now. Thanks🌹

@Matsobane-tc6ii Ай бұрын

Excellent formula to save time

@nobodynobody1235 3 ай бұрын

With the nested if like this, using a reference lookup table is ideal. When I use if statement, I mostly use it as argument so the formula will do a different calc

@marcochimio 14 күн бұрын

I've got dozens and dozens of spreadsheets with massively nested IF statements (up to about 12-times). Why didn't I know about this? Thanks again.

@Xevailo 2 ай бұрын

Note for all german users: apperantly, in the german Version of Excel, this function is intuitively called "ERSTERWERT"

@splendid1963 3 ай бұрын

👍excellent. Very useful. Thanks

@pandharinathjoshi6565 3 ай бұрын

Very neat explanation sir💐🙏

@rameshhariharan2623 2 ай бұрын

Sir this is great

@raylachman8101 2 ай бұрын

thank you sir ‼️👍

@MrHackmed 3 ай бұрын

This is excellent

@artnace 3 ай бұрын

Never heard of it but will try it.

@yousefabdelsamie5983 3 ай бұрын

Perfect, thank you

@aceofdatabase 3 ай бұрын

Knew of switch functions generally... Had no idea it was in Excel. How handy.

@ezkul95828 4 күн бұрын

Cool, SWITCH function is much simpler than nested IF function.

@jasonjackson4555 3 ай бұрын

I love switch(). I told my analyst to try it a year ago and he cleaned up some complex sheets with it.

@TeachersTech 3 ай бұрын

That's awesome!

@sammyliles7216 8 күн бұрын

Looks a lot like the "Case" function in Filemaker Pro...I always wished I had that available in Excel, thanks so much!

@rudycramer225 3 ай бұрын

Nice function. I can use this. Will save a lot of hassles.

@michaelpostell4053 4 ай бұрын

Never heard of this. Thanks for letting us know. (And for your other tips on other posts).

@TeachersTech 4 ай бұрын

So glad you found useful! Your welcome.

@magma2050 20 күн бұрын

That's very handy. I've neither used nor heard of this function, but I will likely make good use of it this week. There are a few fringe cases where I suspect it's no better (e.g., to simulate the output of a decision tree by examining TRUE/FALSE statements in multiple cells, which I think would require as many nested SWITCH functions as nested IF functions to achieve), and as others have said, larger lists are best queried using lookup functions, but this will tidy up smaller lists in my future spreadsheet work significantly.

@stuartc6303 3 ай бұрын

Never heard of it before tbh. Looks useful.

@richardslater677 3 ай бұрын

Interesting and useful

@justdontJD Ай бұрын

I create so many complicated spreads with very long IF statements, the switch function is going to cut my work in half. Thank you for sharing.

@thatguyvince8767 3 ай бұрын

It may be useful for one-off small tables, but for anything larger or repeatable, I'd use the IFERROR and VLOOKUP functions and a separate tab with the reference data (using named ranges for the data areas). Doing that, I'd be able to add to the list of selection items very easily and the data in the entire table change without writing a really long cell formula nor having to update the cell formula once I needed to update my choice criteria.

@Daltonpat 3 ай бұрын

Like most reactions i would probably use xlookup and a table. I do see an advantage when you are using numbers and letters when searching. I will test the speed of switch vs xlookup on a large number of records. Thanks for the video!

@x4ms 3 ай бұрын

Thank you!

@gichai 2 күн бұрын

Good job

@StanEby1 Ай бұрын

Love it.

@JoeLoffredo Ай бұрын

I use CHOOSE for similar situations, but this is useful too.

@vishal80 19 күн бұрын

Thank you 😊

@karenmackstewart 2 ай бұрын

I have been using Excel since 1995 and am always excited to learn new things. For those saying rather use a lookup table - perhaps the example was a bad one. There are many times when we DO use If statements and I can see the value of using SWITCH instead - it is much neater and clearer for editing later. My only constructive feedback is that the title of this video is misleading. The 3rd example shows how IF statements still have value. 🙂

@Srinivasan.Answers 3 ай бұрын

useful information

@user-fq1nj9sb4e 3 ай бұрын

This is great

@carlosmiguel9733 Ай бұрын

Em português essa função é chamada de PARÂMETRO. Excelente!

@Pankaj-Verma- 3 ай бұрын

It's good, thanks.

@cesarrodrigues9763 2 ай бұрын

Very useful...

@kurtcpi5670 3 ай бұрын

I've used "switch" coding in C for 40 years, but I didn't know Excel had that function. I don't know the maximum number of close brackets I've ever had in a nested IF formula, but at some point I usually opted to just switch to a custom VB function. This definitely simplifies things when comparing simple values. I'll have to dig a little deeper to see if it can be used to return a value using calculations/formulas as criteria.

@herumuharman6305 7 күн бұрын

A streamlined version of IFS function, nice.

@zkimyong 3 ай бұрын

I did not know this before!

@md.moslehuddinuddin4077 3 ай бұрын

It's excellent! thank you. From Dhaka, Bangladesh, Md. Mosleh Uddin

@MrMarcusjames 3 ай бұрын

outstanding

@svause 28 күн бұрын

Awesome! I'd gently suggest the next step would be to use lambda() to call the switch statements so that there's only one version of the switch floating around in the examples, rather than multiple copies. Yes?

@mrewan6221 24 күн бұрын

Yes, this would encourage me to use SWITCH. I really don't like having thousands of copies of the same formula with hard-coded values. The alternative is to write a VBA function (which I often do). Or go back to a standard VLOOKUP or INDEX(MATCH()).

@adetone3252 2 ай бұрын

Much clearer than using multiple in-line ifs. I hope the next MS step is to get away from inline functions to make it more readable and easier to debug.