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.
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.
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.
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.
@@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
@@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 “”.
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 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.
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.
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.
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.
watching the examples, I dont see how swapping out a vlookup with switch would be better, except you can see what the formula is doing without looking up tables on a "refrence" sheet.
@@SethWildCard vlookup is quicker to type, simpler and seems more 'clean'...? it's also easier to make correction in the reference table than in the function. I only use "if" if that's true/false question, for all the others I strongly prefer vlookup
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.
@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.
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
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.
@@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 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
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!
I've been using Excel since version 5.0, making complex sheets and workbooks, even using VBA, but everyday I discover new features and tricks. In this case, about 40% of all shown tricks were new for me. Thanks.
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.
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.
Avoid using a switch statement, try using if statement with a VLOOKUP or XLOOKUP already provides the values. Refrain from hard-coding in the formula, etc.
Because your values might change, and instead of changing a bunch of formulas, you change the lookup table once. For example, if Excellent changed to Outstanding, just update the reference table instead of all the formulas. You can also add IFNA at the beginning for a value that’s not found, a la “Not Rated”
You don't have to hard-coded the value. You can use name ranges for both the region values and commission values, to a certain degree of dynamic when using SWITCH(). This is an example with the commission values in named range. SWITCH(E1,"NORTH",C_NORTH,"SOUTH",C_SOUTH,"EAST",C_EAST,"WEST",C_WEST,C_DEFAULT)*D1
WOW! I love Excel and use it everyday for all sorts of purposes. Like many below IF statements are my *b*. I have to go through so many spreadsheets now to replace a lot of them with SWITCH! This is amazing!😁🥰
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
Thank you. I was looking for something totally unrelated but this showed up as a post, and it looked like something I needed to know. Boy did I. Thank you a ton.
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!
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. 🙂
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!
Liked the innovative video, thanks for the info. In the last example you still made use of IF statement, so how do you say goodbye to IF? Can we do last example without using IF? Thanks for elaborating.
I'm guessing it's the same benefit as in other languages. Essentially, the runtime process only needs to access the variable once instead of during each conditional.
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.
Damn it! I wish I saw this a week ago. I just wrote some very long IF statements for my risk assessment tool... At least I have this now as I'm sure I'll be using it soon enough.
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?
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()).
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!
It's good for Example 2 and 3 where the values are Text, but for Example 1 I would use CHOOSE, nested within an IFERROR statement to get the Not Rated result.
I use if to access information from different source sheets based on a criteria for example country name E.g., if(a2="Austria", vlookup..., if (a2="Belgium", vlookup... It seems that the example you provide above might bless intensive and memory swapping
You haven't mentioned how many commas is the limit if there is one (pending to be checked). Nested IFs can go upto 7 if I'm not mistaken, the switch will make things easier if there is no or much larger limit. Thanks for the video. Really good.
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.
I've never seen this function before...Sweet, Easy, I will begin using it when I can ...Are there any situations where the "If" statement is still a better choice?
Love the SWITCH command. i have created many multiple level nested loop IF statements to cover many conditions. Problem was at time missing a comma or adding one. Missing a parenthesis or adding one. Figuring it out when linear a big issue. Sometimes I put the formula in MS Word using Courier to show each level in comparison to other levels to figure out what I did was a bit much. Love SWITCH and a lot of other new commands. About time! Using spreadsheets since VISICALC 1979 before computer existed, kidding!
Thank you so much...this looks to be a really useful function...I am going to change all my IF function formulas today. Out of curiosity, can you let me know if there is any situation where IF would be better than SWITCH?
You're welcome! IIF is better suited for scenarios requiring complex logical tests that involve AND, OR, NOT, or a combination of these. SWITCH does not directly support these without nesting additional functions, making IF a clearer choice for such conditions. Another would be you can perform different calculations or apply different formulas in the true and false parts of an IF statement, which might be more cumbersome with SWITCH. But it's certainly work a think each time you plan an IF statement.
- [0:22] 💡 The SWITCH function in Microsoft Excel can replace nested IF statements, offering a more streamlined and efficient way to handle multiple conditions. - [3:25] 🔄 The SWITCH function can be used for operations like multiplication based on different conditions, providing flexibility and efficiency in calculations. - [5:31] 📊 Combining an IF statement with a nested SWITCH function allows for complex conditional logic in Excel, making it possible to assign values based on multiple criteria while handling blank cells efficiently.
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.
In your first example, I would have a worksheet with a set of data that has the number in one cell and it's word in the next cell then I would do a VLookup. That way I could adjust one place and effect everywhere that uses it. Not sure if mine or yours is the most energy efficient in terms of calculation?
Didn't know about the switch, thank you, but would also not use it in these instances as it just causes more maintenance work later on. Some reference table / lookup will make it much easier to maintain if situations change.
It's a useful function, but if you're going to repeat it down the page, you make changing/adding/deleting options later a little more work. I'd rather use a lookup table in this case so you only have to change one cell, not many.
Super helpful tip. Just subscribed. Can Switch look up a table? If I need to switch too many values, it can be beneficial that Switch can look up a predefined "Switch" table.
Why wouldn't a vlookup table work in place of switch. Seems like it does the same thing but easier to update values if needed without having to change each formula. Thanks.
Terrific, very impressive! Seeing as you know what you're talking about, and you make things very simple, I was just wondering if you could do a different video which I haven't found anywhere. This deals with inventory and sales, where each item has multiple colors. It would be great to show how to subtract a certain color from the inventory when a sale is made in the sales tab without listing each item multiple times per item. Just a suggestion. 😇
What an excellent video! For me I only use switch when a vlookup table won't do the job (like when a default is required). The reason for this is a vlookup table lets you put all of the values on the sheet, instead of in a function and other formulas can use the same values. I tend to make a named reference for anything that's in a vlookup too, so I know not do delete it by mistake and having it easily available in any sheet :)
With XLOOKUP you have a default - to me, the SWITCH function looks pretty useless. What I'd like to have would be a CASE function - CASE(expression1, value1, expression2, value2, ..., default_value)
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.
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
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.
you can use =IFS() as well so they don't have to be nested and it looks more like this switch statement
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.
@@AlThePal78Single Ifs can work just return the "" and concatenate with the other Ifs.
I use SWITCH it in Power Bi. I normally use IFS to avoid nested if statement. Great one!
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
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.
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.
@@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
For that, you can encapsulate the function with IFNA or IFERROR :)@@alexk7467
@@alexk7467 You can set the final test value to "TRUE()" then the result: =IFS(test1, result1, test2, result2, ..., TRUE(), result_n)
@@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 “”.
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.
How can you use XLOOKUP to insert values based on criteria in other cells? SWITCH has a different function it seems to me.
@@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.
@@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.
Exactly what I tought while watching the video.
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.
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.
I’m glad you found it useful!
SWITCH is a new function not find in older version of excell
@@tomjones1502 This function only in 365 right?
No, SWITCH function was added in year 2016, in excel 365
Switch function is in Excel 2019 and 365 (not sure but 2021)
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.
I do the same
Same too
watching the examples, I dont see how swapping out a vlookup with switch would be better, except you can see what the formula is doing without looking up tables on a "refrence" sheet.
@@SethWildCard vlookup is quicker to type, simpler and seems more 'clean'...? it's also easier to make correction in the reference table than in the function. I only use "if" if that's true/false question, for all the others I strongly prefer vlookup
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.
I would agree that.
@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.
I use Switch in PowerShell all the time, but funnily it never occurred to me that it exists in Excel.
Really useful, thanks!
switch is also available in many other programming languages
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
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.
@@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.
@@Pronellyllon switch function is inflexible and undynamic
@@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
Sweeeet !!! What a great find ! Thank you. After using the IFs for 30+ years this is a welcome new tool.
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!
I've been using excel for 20 years and haven't used this. Amazing! Thanks!
I've been using Excel since version 5.0, making complex sheets and workbooks, even using VBA, but everyday I discover new features and tricks.
In this case, about 40% of all shown tricks were new for me. Thanks.
Excellent, clear and comprehensive explanation! Thank you!
Thanks!
Thank you so much! I really appreciate it.
@@TeachersTech and I appreciate your helpful efforts!
Using excel for decades but today I learned about switch. Thanks ❤
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.
wow, wow, wow, i have been looking for this for years. you have made me the happiest man ever ever. God bless you!!!
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.
You explain things very well and it's easy for me to learn from your videos despite my adhd. Thank you!
You’re welcome!
Avoid using a switch statement, try using if statement with a VLOOKUP or XLOOKUP already provides the values. Refrain from hard-coding in the formula, etc.
Why?
Because your values might change, and instead of changing a bunch of formulas, you change the lookup table once. For example, if Excellent changed to Outstanding, just update the reference table instead of all the formulas. You can also add IFNA at the beginning for a value that’s not found, a la “Not Rated”
@@JeffUmscheid I've got a lot yet to learn.
Dynamic ✅
You don't have to hard-coded the value. You can use name ranges for both the region values and commission values, to a certain degree of dynamic when using SWITCH().
This is an example with the commission values in named range. SWITCH(E1,"NORTH",C_NORTH,"SOUTH",C_SOUTH,"EAST",C_EAST,"WEST",C_WEST,C_DEFAULT)*D1
WOW! I love Excel and use it everyday for all sorts of purposes. Like many below IF statements are my *b*. I have to go through so many spreadsheets now to replace a lot of them with SWITCH! This is amazing!😁🥰
Never knew about the switch function before this video. After watching your tutorial, I will incorporate this function into my repertoire! Thanks!😄
This is the first time I am getting to know switch function. Thanks a lot for the insights
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
Thank you. I was looking for something totally unrelated but this showed up as a post, and it looked like something I needed to know. Boy did I. Thank you a ton.
Excellent. I already have a couple of work cases that can benefit from this SWITCH function!
Super helpful video. Excellent examples
Never knew about this function.. Very useful. Thanks a ton for the detailed video. Much appreciated. Just taking out nested IFs is a great feeling.
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!
You're so welcome!
Thank you RUclips algo! I actually can use this Monday at work... completely useful and relevant. Very nice examples!
Great to hear! Thanks!
SWITCH is nice, but I'm more a fan of IFS. Nice video ❤🙏🏽
Hey! this is simpler than nested if function. THANK YOU!
I had no idea! Was struggling with “IF” just this week. I can now simply with Switch!! Thanks. 😊
Thank you very much. I didn't know about this formula but will definitely use it in the future.
Glad it was helpful!
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. 🙂
This video made me realize the switch function isn't the one I was looking for, it was IFS. Thank you!
I love switch(). I told my analyst to try it a year ago and he cleaned up some complex sheets with it.
That's awesome!
Great, I love it. I am using excel 20+ years and I did not hear about that, thx.
I have not heard of the switch function...THAT was excellent! Thank you!
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!
Never heard of the Switch function. Thank you Jaime!!! :)
You're welcome!
Never heard about Switch function. Thanks for the video. Very clear and well explained!
I have a complex if/index/match with 10 different instances, so this might make my life easier. Thanks!
Liked the innovative video, thanks for the info.
In the last example you still made use of IF statement, so how do you say goodbye to IF? Can we do last example without using IF? Thanks for elaborating.
Game changer! Thanks. I have sooooo many nested IF statements and they are a bear to update.
I'm guessing it's the same benefit as in other languages. Essentially, the runtime process only needs to access the variable once instead of during each conditional.
i did a very big formula with if + if + if. with yours i can make it very short.. thanks a lot. definitly a like
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.
Damn it! I wish I saw this a week ago. I just wrote some very long IF statements for my risk assessment tool... At least I have this now as I'm sure I'll be using it soon enough.
This is AWESOME!!!! Was able to simplify so so soooooo many IF statements in a spreadsheet I am working on.
Glad it was helpful!
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?
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()).
Never used switch. Liked and subscribed. Good input.
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!
Wow what a time saver! Truly good by nested IFs lol thanks for sharing!!
It's good for Example 2 and 3 where the values are Text, but for Example 1 I would use CHOOSE, nested within an IFERROR statement to get the Not Rated result.
I use if to access information from different source sheets based on a criteria for example country name
E.g., if(a2="Austria", vlookup..., if (a2="Belgium", vlookup...
It seems that the example you provide above might bless intensive and memory swapping
Thank You. This will make my sheets so less complicated now.
Thanks for the demo. I can see some scenarios where this could help.
Absolutely excellent info. I want to try it on some spreadsheets that have some complex nested if statements
You haven't mentioned how many commas is the limit if there is one (pending to be checked). Nested IFs can go upto 7 if I'm not mistaken, the switch will make things easier if there is no or much larger limit.
Thanks for the video. Really good.
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.
I've never seen this function before...Sweet, Easy, I will begin using it when I can ...Are there any situations where the "If" statement is still a better choice?
Great examples! I had never heard of switch.
Love the SWITCH command. i have created many multiple level nested loop IF statements to cover many conditions. Problem was at time missing a comma or adding one. Missing a parenthesis or adding one. Figuring it out when linear a big issue. Sometimes I put the formula in MS Word using Courier to show each level in comparison to other levels to figure out what I did was a bit much. Love SWITCH and a lot of other new commands. About time! Using spreadsheets since VISICALC 1979 before computer existed, kidding!
Thank you so much...this looks to be a really useful function...I am going to change all my IF function formulas today. Out of curiosity, can you let me know if there is any situation where IF would be better than SWITCH?
You're welcome! IIF is better suited for scenarios requiring complex logical tests that involve AND, OR, NOT, or a combination of these. SWITCH does not directly support these without nesting additional functions, making IF a clearer choice for such conditions. Another would be you can perform different calculations or apply different formulas in the true and false parts of an IF statement, which might be more cumbersome with SWITCH. But it's certainly work a think each time you plan an IF statement.
- [0:22] 💡 The SWITCH function in Microsoft Excel can replace nested IF statements, offering a more streamlined and efficient way to handle multiple conditions.
- [3:25] 🔄 The SWITCH function can be used for operations like multiplication based on different conditions, providing flexibility and efficiency in calculations.
- [5:31] 📊 Combining an IF statement with a nested SWITCH function allows for complex conditional logic in Excel, making it possible to assign values based on multiple criteria while handling blank cells efficiently.
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.
I use choose on a match {list} to select values or formulates or entire let expressions
In your first example, I would have a worksheet with a set of data that has the number in one cell and it's word in the next cell then I would do a VLookup. That way I could adjust one place and effect everywhere that uses it. Not sure if mine or yours is the most energy efficient in terms of calculation?
Great function! Thanks a lot, I didn't know about this
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.
This is great, I'll have to practice this after I watch it a few more times.
Nice video but don't put so much data in a formula, use a referencable and updatable data table and xlookup
…it’s a made up example
That's what I came to the comments to find out. This just seems like an XLOOKUP that's more cumbersome to maintain.
Using lookup tables with Search and also Filter or IsNumber so you create a spill formula based on a predetermined range 🤠🤠
On the other side my arguments are long and this shows exactly what I need.
Probably a good formula for small list. No more than 3. I would use a xlookup with a proper match mode
Very helpful teacher. This is the 1st time I know about this function
Never heard about it! Excited to know and use it..
Awesome Tutorial, so much easier.
Didn't know about the switch, thank you, but would also not use it in these instances as it just causes more maintenance work later on. Some reference table / lookup will make it much easier to maintain if situations change.
It's a useful function, but if you're going to repeat it down the page, you make changing/adding/deleting options later a little more work. I'd rather use a lookup table in this case so you only have to change one cell, not many.
Brilliant - Thanks for sharing. Really appreciate your work. Kind Regards
Note for all german users: apperantly, in the german Version of Excel, this function is intuitively called "ERSTERWERT"
Super helpful tip. Just subscribed. Can Switch look up a table? If I need to switch too many values, it can be beneficial that Switch can look up a predefined "Switch" table.
Nice video. Is it necessary to put isblank or can we put blank?
So very helpful! Excellent presentation regarding a function I never heard of...
Glad it was helpful!
Why wouldn't a vlookup table work in place of switch. Seems like it does the same thing but easier to update values if needed without having to change each formula. Thanks.
Just use xlookup instead with a table. More dynamic and flexible I think. But anything that gets rid of nested IFs is an improvement
Well Explained really easy and efficient with practice file
Very interesting and well-presented. Thanks!
I have heard of it and used it in VBA only but not in a spreadsheet
Thanks!
Extremely well explained and perfect real life examples
Knew of switch functions generally... Had no idea it was in Excel. How handy.
Never heard of this one. Looks great! Is this a new function from the Microsoft 365 office package?
I hav never used Switch before. But I use IFS a lot. I see that this solves what's missing with IFS, which is the ELSE part.
With IFS, you can use "TRUE" as your final condition with a value to cover when all conditions are false...
this is absolutely awesome man, reduced the entire work.
Terrific, very impressive!
Seeing as you know what you're talking about, and you make things very simple, I was just wondering if you could do a different video which I haven't found anywhere. This deals with inventory and sales, where each item has multiple colors.
It would be great to show how to subtract a certain color from the inventory when a sale is made in the sales tab without listing each item multiple times per item.
Just a suggestion. 😇
Looks great! I'm guessing you can nest v(x)lookup or sumifs in there as well?
I like this video ❤❤❤❤❤❤❤
What an excellent video!
For me I only use switch when a vlookup table won't do the job (like when a default is required). The reason for this is a vlookup table lets you put all of the values on the sheet, instead of in a function and other formulas can use the same values. I tend to make a named reference for anything that's in a vlookup too, so I know not do delete it by mistake and having it easily available in any sheet :)
With XLOOKUP you have a default - to me, the SWITCH function looks pretty useless. What I'd like to have would be a CASE function - CASE(expression1, value1, expression2, value2, ..., default_value)
@@brdrnda3805 I believe the SWITCH() is the same as CASE().
@@laurencesyeng6182 Just compare Excel's SWITCH function with the CASE statement proposed by me. It's different.
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.