Excel - SWITCH Function

SWITCH was a function which was added in Excel 365 as part of 6 new functions in Excel. These functions were announced in Feb-2016 by Microsoft - 6 new Excel functions that simplify your formula editing experience . Hence, this function is available in Excel 2019 as well as Excel 365. If you try to use in an Excel version prior to Excel 2019/365, then you would get #NAME error.

The purpose of SWITCH was to simplify writing of nested IF statements. The basic syntax of SWITCH is

=SWITCH(Value to switch, Value to match1...[2-126], Value to return if there's a match1...[2-126], Value to return if there's no match) where you can evaluate up to 126 matching values and results ( Microsoft documentation - SWITCH function ).

I find that this works like CASE - SELECT statement of VBA. Many programming language do contain SWITCH function which Excel SWITCH tries to emulate for example, Java has a SWITCH function. VBA has implemented SWITCH function as CASE - SELECT.

Suppose, you are having following requirements -

Cell A2 contains the rating between 1 to 5. Display following depending upon what is contained in cell A2

1 = Poor, 2 = Average, 3 = Good, 4 = Very Good, 5 = Excellent

You will write following IF formula to accomplish this

=IF(A2=1,"Poor",IF(A2=2,"Average",IF(A2=3,"Good",IF(A2=4,"Very Good",IF(A2=5,"Excellent","")))))

The above statement can also be written in a simplified way using SWITCH

=SWITCH(A2,1,"Poor",2,"Average",3,"Good",4,"Very Good",5,"Excellent","")

The same can also be written using IFS

=IFERROR(IFS(A2=1,"Poor",A2=2,"Average",A2=3,"Good",A2=4,"Very Good",A2=5,"Excellent"),"")

But notice 2 things - IFS demand that condition needs to be written all the time. Hence A2=1, A2=2 and so on will have to be written. Also IFS doesn't supply a default argument to fill in if a condition is not met. This I am ensuring in the above formula by IFERROR.

Hence, in this case, SWITCH is superior to IFS.

But SWITCH has a weakness. For SWITCH, it has to be equal to condition. Hence, it evaluates A2=1, A2=2 and so on as part of its internal working though this is not written explicitly in the formula whereas these conditions need to be written explicitly in IFS. SWITCH will not support <. >, <=, >=, <> operators (There is a work-around below)

Suppose you have following requirement - Give a grade A to a student for marks above 90, B for marks above 80, C for marks above 60, D for marks above 40 and F for marks below 40. Then following formulas using IF and IFS can be used

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=60,"C",IF(A2>=40,"D","F"))))

=IFS(A2>=90,"A",A2>=80,"B",A2>=60,"C",A2>=40,"D",A2<40,"F")

Now since other than equal to operation is not supported by SWITCH, hence SWITCH can't be used. But there is a work-around which can be used. You can use following formula using SWITCH to support other than equal to operators.

=SWITCH(TRUE,A2>=90,"A",A2>=80,"B",A2>=60,"C",A2>=40,"D","F")

Now see, compared to IFS, it avoids one condition less which is A2<40.

This is a good function to give ratings or rankings on the basis of a given value or range (best is when you need to evaluate equal to conditions). Like

=SWITCH(TRUE,A2>=18,"Adult",A2>=13,"Teens","Child")

Equivalent IF and IFS statements are

=IF(A2>=18,"Adult",IF(A2>=13,"Teens","Child"))

=IFS(A2>=18,"Adult",A2>=13,"Teens",A2<13,"Child")

A function of Excel can be combined with other Excel functions. Hence, SWITCH can also be combined with other Excel functions. Few examples -

=SWITCH(TODAY()-A2,1,"Yesterday",0,"Today",-1,"Tomorrow","Any other day")

=SWITCH(TRUE,A2<TODAY(),"Past",A2=TODAY(),"Current",A2>TODAY(),"Future")