Excel SWITCH Function - Nested IF's Just Got Easier
ฝัง
- เผยแพร่เมื่อ 16 ต.ค. 2024
- The SWITCH function provides a cleaner and more concise alternative to nested IF functions.
Learn ALL the important Excel functions - amzn.to/3Rg87Go
The SWITCH function refers to the expression just once, whilst multiple nested IF functions would refer to the expression in each logical test.
It then runs through a list of tests and resulting actions.
It is similar to the CHOOSE function, but CHOOSE is limited to using an index number only as the expression
The SWITCH function can handle text or a number as the expression.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
Thanks Alan.. another excellent video. Never heard of =Switch() before today. Thanks for the introduction to this new function. Thumbs up!
Thank you Wayne.
Great examples Alan. Can't wait to use this in nested functions!
Awesome, thanks Angel.
Your video tutorials surpass all others I watch. They are clear. The applications power is well demonstrated. Got a new subscriber.
Ops looks like I already subscribed. IF(I could do it again, I would, keep subscription)
ha ha thank you. Your support is appreciated.
I do not want to miss any of your tutorials ..🌟 🌟 🌟 🌟
Glad I found this video, it was a bit confusing when I first found out about the SWITCH function. The 2nd example is the one that really clear things up. The 1st example: using VLOOKUP on a table ‘mapping the colour to relevant %’ seem like a wise choice, especially if there’re larger # of pairs. I mean, I rather list 126 pairs in a 2C126R table to use VLOOKUP over putting 250+ values inside the SWITCH function! This was in fact my main confusion of why use a SWITCH function. The 2nd example where ‘value to return’ is a Range, this is what makes switch a good choice. Without using the SWITCH function, I would most likely use the approach ExactProBi pointed out.
Alan. I am so grateful to you. 👍 🌟 I never stop learning from you. You are like an Excel encyclopedia. Stunning. Amazing. Superb. Great. You are such a talented teacher. Best regards. Salim
Thank you very much Salim.
Hi ,Great thanks for sharing this video on TH-cam on "Switch" function in excel. In the second example where you shown the how to use Switch function with Vlookup, there I tried to make the formula a bit slim by using indirect function--- =VLOOKUP(C2,SWITCH(B2,"Red",Table1,"Green",Table2,"Yellow",Table3),2,TRUE), instead of =VLOOKUP(C2,SWITCH(B2,"Red",$H$2:$I$6,"Green",$N$2:$O$6,"Yellow",$K$2:$L$6),2,TRUE)...wherever in the formula there is range like $H$2:$I$6, I replaced it with Table1, Table2, Table3 respectively!
This made my chart so much better ! Thanks.
Awesome! You're welcome 😊
Hi Alan, a very useful tutorial, many thanks for sharing, appreciate it very much. Cheers Mohideen
Thanks Mohideen.
Wonderfull demonstration and function. I'd whish I'd seen this video when it came out. Thanks!
Thank you Fabio. Much appreciated.
Wow, this just made my workload easier! Thanks 😀
Excellent! That is great to hear Shueyb.
Good examples--especially the VLOOKUP example. I've never made use of SWITCH. Do you use it much?
Thank you Oz. Not a huge amount. I like to mix things up and give each function its chance in the limelight though.
What is it looking up for in the tables?
Could you explain the lookup relation to the tables reference to column C
Thank you very much for uploading this video. This is very helpful.
You're welcome Gopala.
Dynamic table array was a pain when we had to use Named ranges with indirect function..... switch has solved that issue, thanks for the video...
Great, thanks ExactPRO.
Thanks for this. In the last part, how can you do linear interpolation instead of choosing the closet values?
NIce tutorial. I think you are considering the C column as the order quantity, not price?
Yeah I got myself a little mixed up with the 2 :) but you get what I am trying to say. The logic still works.
That's cool. It will be interesting to see if the Microsoft folks heed your "suggestion" and enhance it with logical operators ;)
That would be awesome. They should listen to me :)
Sir respect from India. Great tutorial.
Thanks Raju.
Very useful - thanks.
You're welcome, thanks.
Great! Thanks, Alan!
You're welcome Sandy.
Am I understanding it right that in "values" you cannot use cells? I tried and gives back a value error.
Thanks a lot Sr.!
You're very welcome, Juan.
Thanks a lot sir for the update
You're welcome Sabbasachi.
what a fantastic function, thank you so much for this video! subscribed to your page :) be well!
Thank you Vanarum.
Wow .Thanks for video....
You are more than welcome Sandeep.
Thanks sir!!
You're welcome Vipin.
how did it know which amount of discount to apply? it says it depends on number ordered but i dont see that corresponding to the people
VLOOKUP was being used as a range lookup. Looked up the price in the ordered column to see where the number fell in the ranges.
Does anyone know how to choose this function (or if it even exists) in a german Excel Version? Couldn't find any help so far.
There is a function translator for Excel in different languages - support.office.com/en-ie/article/excel-functions-translator-f262d0c0-991c-485b-89b6-32cc8d326889
SWITCH is also quite new too. Needs Office 365.
Thank You very much
You're welcome Jidonu.
what's the difference between ifs() and switch()
SWITCH is great because you list an expression/test once. Then test it against a list of matches. IFS require you to enter multiple logical tests, but is far more flexible and not limiting you to exact matches.
The price number is not a perfect match with the ordered numbers in the discount tables. How does the function know what part of the range the price falls in. Is it because the "true" argument of the Vlookup? But wouldn't the true get the approximation above but also below the ordered number? (Not sure if I stated my question clearly)
Yes it is because of the True argument of VLOOKUP. This argument returns the closest less than. So if it looked for number 26 in a range of 0, 25 and 50 it would return from 25.
This is known as a range lookup up because it returns from within a range, or within a threshold.
Haa I didn't know about the "less than" for the true argument (In my work I usually look for exact matches). Thanks a lot, great video!
You're welcome NHKprod. Yes range lookups are rare. Most people look for a specific thing.
Thanks you sir,
You're welcome Nagendra.
Anyone know why this function is not recognised in Office Pro Plus 2016?
It is only available with the Office 365 subscription at the moment.
thus very powerful!!
Thank you Samuel.
Name ranges would have made life easy. (switch with vlookup)
When it's a 95 degrees, You give them the power to shut off your cooling. You then FRY!
It seems SWITCH doesn't exist on Excel, it only exists in Office 360.
It is available from Excel 2019.
you skip a step!! What about the result ??
Result?