Excel SWITCH Function - Nested IF's Just Got Easier

The SWITCH function provides a cleaner and more concise alternative to nested IF functions.

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;

*** Online Excel Courses ***

Connect with us!

[TY-zlvw]

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.

[smartshanu]

Hi, Great thanks for sharing this video on YouTube 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!

[wayneedmondson]

Thanks Alan.. another excellent video. Never heard of =Switch() before today. Thanks for the introduction to this new function. Thumbs up!

[sasavienne]

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

[qaz]

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)

[AngelSanchez-igjt]

Great examples Alan. Can't wait to use this in nested functions!

[ChristianrnstrupRasmussen]

This made my chart so much better ! Thanks.

[shueybamin]

Wow, this just made my workload easier! Thanks 😀

[mohideenthassim]

Hi Alan, a very useful tutorial, many thanks for sharing, appreciate it very much. Cheers Mohideen

[ospino]

Wonderfull demonstration and function. I'd whish I'd seen this video when it came out. Thanks!

[sasavienne]

I do not want to miss any of your tutorials ..🌟 🌟 🌟 🌟

[ExactProBi]

Dynamic table array was a pain when we had to use Named ranges with indirect switch has solved that issue, thanks for the video...

[gopalakrishnarao]

Thank you very much for uploading this video. This is very helpful.

[rajuar]

Sir respect from India. Great tutorial.

[OzduSoleilDATA]

Good examples--especially the VLOOKUP example. I've never made use of SWITCH. Do you use it much?

[ForTheWin]

Thanks for this. In the last part, how can you do linear interpolation instead of choosing the closet values?

[prashponnappa]

What is it looking up for in the tables?
Could you explain the lookup relation to the tables reference to column C

[kevingodsave]

NIce tutorial. I think you are considering the C column as the order quantity, not price?

[Foreman]

Am I understanding it right that in "values" you cannot use cells? I tried and gives back a value error.

[NephilimShroud]

what a fantastic function, thank you so much for this video! subscribed to your page :) be well!