2 Awesome Tips on How to Use the SWITCH Function in Excel

[@BIGorilla]

Do you have any other ways in which you use the SWITCH function? Fun to share? Let me know in the comments!

[@Rice0987]

If you have simple progression, like in this case, you may use CHOOSE function, but when you have specific values, you can use SWITCH. Thanks for updating. :)

[@dunnobe]

SWITCH(TRUE;)) is fantastic. I found out about this in DAX-formulas and was very happy to see that this was added to Excel as well. I'm quite surprised that there is not much info to be found on google/youtube about this powerful formula.

[@7213261]

Thanks Rick!

[@ExcelOffTheGrid]

Great video Rick. My favorite feature of SWITCH is that it returns the cell references when the value returned is a range. Therefore, we can use SWITCH inside a named range to create dynamic ranges, which are great for flexible formulas or charts.

[@BIGorilla]

Thanks for sharing Mark. So SWITCH can return cell references, I didn't know that! Could that mean that if the named range is used in column f it returns a different range than used in column g? If you test a criteria using the COLUMN() function for example

[@ulludacharkha]

Thanks for Sharing the tip Mark. Can you site an example please !

[@metcaelfe]

Thanks for this explanation! What are your thoughts of SWITCH vs IFS?

[@BIGorilla]

I love both! Personally I started using SWITCH before I used IFS. I wote a blog articl about it here: gorilla.bi/excel/switch-function/. Then IFS came and I sticked with SWITCH. Not because there's anything wrong with IFS, but out of habit. Rick

[@yehuditmahalal2070]

For the weekdays you can use simple way: =SWITCH(TRUE,AND(M26>=6,M26

[@pascalmenezes]

How would I use the Switch function with an AND or an OR. E.g. If(A = ("Xxx" or "Yyy"),"good","bad")

[@rvgiri]

Good function to use, but this seems hidden, i never noticed excel this function.

[@BIGorilla]

Great you found it, enjoy excel!

[@Foreman1329]

Why switch doesn't work if I use a cell reference as a value input?(the second parameter of the function). It only works if I hardcode the value and type it in the formula. This can't be how it's intended to work...

[@BIGorilla]

Hi Foreman. Can you share an example formula? Switch is supposed to support using cell references. Best Rick