SWITCH() Function in Excel and 3 Alternatives

Excel Courses: www.teachexcel.com/premium-co...
Excel File: www.teachexcel.com/excel-tuto...
Microsoft Office Doc: support.microsoft.com/en-us/o...
How to use the SWITCH() function for Excel.
SWITCH() allows you to match a series of values and output a value based on that match - this replaces basic nested IF statements in Excel.
This tutorial covers the syntax of the switch function and shows you how to use it. As well, 3 robust alternatives to the switch function are also covered and explained.
The benefit to showing you this function and its alternatives is to allow you to have the tools that you need to use the best formula for the job. Sometimes you may want a complex setup that is more robust and versatile and utilizes a VLOOKUP() and a lookup table and sometimes you want an old-school IF statement and sometimes, the new SWITCH() function is the perfect tool for the job.
15 IF Statement Examples: • 15 IF Statement Exampl...
5 Bite-Size Vlookup Tricks: • 5 Bite-Size Vlookup Tr...
IFS() Function: • IFS() Function Excel -...
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
TeachExcel.com
#msexcel #howto #tutorial

[@wayneedmondson1065]

Thanks! Another good option is IFS() as in: =IFS(A15=1,"Good",A15=2,"OK",A15=3,"Bad",A15=4,"Not Allowed"). Simplifies vs. so many nested IF() formulas. Thanks for sharing :)) Thumbs up!!

[@TeachExcel]

Thanks for the great comment Wayne! You're spot-on with the alternative and that is a great example, as usual :)

[@kathyroberts7777]

Thank you for showing new and old. Your tutorials are teaching me so much more than I could ever learn on my own.

[@hazemali382]

always Simple and great ♥

[@andrewevans9002]

There are many ways of completing a task in Excel and it depends on the number of values you are looking up against. Another option where there are a low number (though like VLOOKUP can be used with ranges) is the LOOKUP command itself. An example would be =IFERROR(LOOKUP(A33,{1,2,3,4},{"Good","OK","Bad"}),"Not Allowed") - Where A33 to A36 would have the numbers 1 - 4, and of course it is useful to test for errors as well by testing with other numbers as well. Nice of you to create the tutorial as it is always useful to have as many alternatives to perform a task as possible.

[@TeachExcel]

Love the lookup example Andrew, thanks for the input!! I bet we could come up with 20 different ways to make a formula like this lol.

[@suecampanelli311]

I learned something new today! Never had heard of SWITCH or CHOOSE!

[@TeachExcel]

I'm really glad I could teach it to you :) I've found that CHOOSE comes in quite handy when you least expect it.

[@HangLe-of4sm]

Thank you for sharing!

[@TeachExcel]

You're very welcome Hang! :)

[@patrickschardt7724]

It’s great for checking for multiple simple values in the same cell or range and returning a corresponding value. It fails instantly if more than one type of comparison is needed Great tutorial

[@TeachExcel]

Thanks Patrick! Yea, it is quite a limited function for sure.

[@rtificial8292]

Beautiful. Thank you

[@TeachExcel]

You are most welcome! I'm glad you liked it :)

[@maomxesoax2471]

Nice. Is there a way to get the format of a cell (font, color etc.) using a formula?

[@darrylmorgan]

Cool Tutorial! I Usually Use SWITCH() In DAX So Much Easier Than Nested IF()...Thank You Sir ;)

[@TeachExcel]

Thanks for the input Darryl, that's a great use-case for this function! Glad you liked the tutorial :)

[@micheltw]

You are the McGiver of EXCEL

[@TeachExcel]

haha I'll take it :)

[@Excelambda]

Great video!! Indeed SWITCH is for exact matches as you said. If we need comparative operators IFS can be used. Example with "forced" comparative operators to prove the point, in cell B4 =IFS(A4:A7

[@TeachExcel]

What a great comment as usual!!! And that last formula haha it killed me! Fun little formula! :)

[@micheltw]

Why doesn't it work for me, I had to put ";" for the first two numbers CHOOSE({1;2}...and I get #N/A for the two last cells.

[@micheltw]

Got it was in french and inverted slash (\) Was to use instead of comma 🤦

[@Excelambda]

@@micheltw Glad you sorted it out. 😊✌

[@vikaasb2016]

Wow !

[@TeachExcel]

:)

[@pierreblanchard9752]

Nice comparaison. We could use the function XLOOKUP instead HLOOKUP. The advantage is there is a parameter for the case ELSE. We won't have to use the function ISERROR.

[@TeachExcel]

Very nice comment Pierre! The new functions are simply superb :)

[@mayurpatilmax007]

Nice one but you missed IFS...

[@TeachExcel]

I thought about that but I was really trying to keep it a bit simple and IFS allows for many types of conditional checks. But I did go ahead and put a link to my tutorial on IFS in the description of this video.