Excel SWITCH function - the compact form of nested IF statement

This article introduces you to the Excel SWITCH function, describes its syntax and provides a couple of use cases to illustrate how you can simplify writing nested IFs in Excel.

If you ever spent far too much time, trying to get a nested IF formula, you'll like using the freshly released SWITCH function in Excel. It can be a real timesaver in situations where complex nested IF is needed. Earlier available only in VBA, SWITCH has been recently added as function in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.

Note. Currently, the SWITCH function is available in Excel for Office 365, Excel Online, Excel 2019 and Excel 2016 included with Office 365 subscriptions.

Excel SWITCH - syntax

The SWITCH function compares an expression against a list of values and returns the result according to the first matching value. If no match found, it's possible to return a default value which is optional.

The structure of the SWITCH function is as follows:

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

It has 4 arguments one of which is optional:

  • Expression is the required argument compared against value1…value126.
  • ValueN is a value compared against expression.
  • ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
  • Default is the value returned if no matches have been found in the valueN expressions. This argument doesn't have a corresponding resultN expression and must be the final argument in the function.

Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.

The SWITCH function vs. nested IF in Excel with use cases

The Excel SWITCH function, as well as IF, helps specify a series of conditions. However, with this function you define an expression and a sequence of values and results, not a number of conditional statements. What is good with the SWITCH function is that you don't need to repeat the expression again and again, which sometimes happens in nested IF formulas.

While everything is ok with nesting IFs, there are cases where the numbers of conditions for evaluation make building a nested IF irrational.

To demonstrate this point, let's have a look at the use cases below.

Say, you have several acronyms and you want to return the full names for them:

  • DR - Duplicate Remover
  • MTW - Merge Tables Wizard
  • CR - Combine Rows.

The SWITCH function in Excel 2016 will be quite straightforward for this task.
Use the Excel Switch function to return full names for acronyms

With the IF function you need to repeat the expression, so it takes more time to enter and looks longer.
Return full names for acronyms using nested If in Excel

The same can be seen in the following example with the rating system where the Excel SWITCH function looks more compact.
Return values for rating scores with the switch function

Return values for rating scores with the If function

Let's see how SWITCH works in combination with other functions. Suppose, we have a number of dates and want to see at a glance if they refer to today, tomorrow, or yesterday. For this we add the TODAY function that returns the serial number of the current date, and DAYS that returns the number of days between two dates.

You can see that SWITCH works perfectly for this task.
Return values for dates using Excel SWITCH

With the IF function, the conversion needs some nesting and gets complex. So the chances of making an error are high.
Return values for dates using nexted Ifs in Excel

Being underused and underestimated, Excel SWITCH is a really helpful function that lets you build conditional splitting logic.

If you need more examples of using nested Ifs, have a look at

You may also be interested in:

14 Responses to "Excel SWITCH function - the compact form of nested IF statement"

  1. Wendy says:

    How can I write an if statement for the following:
    C4 thru F4 will either contain a 1, 2, 3, 4, or 5. If any of those cells have a 1, then G4 should be 1, if there isn't a 1, then if any of those cells have a 2, then G4 should be 2, if there isn't a 1 or 2, then if any of those cells have a 3, then G4 should be 3, if there isn't a 1, 2 or 3, then if any of those cells have a 4, then G4 should be 4, if there isn't a 1, 2, 3 or 4, then if any of those cells have a 5, then G4 should be 5. If there isn't a 1, 2, 3, 4, or 5 then G4 should say no answer.

  2. Paul says:

    =IF(MIN(C4:F4)<=5,MIN(C4:F4),"No Answer")

  3. Paul says:

    Just in case cells C4 through F4 can contain numeric values other than 1 to 5 (or a blank cell) then you could use this.

    =IF(COUNTIF(C4:F4,1)>0,1,IF(COUNTIF(C4:F4,2)>0,2,IF(COUNTIF(C4:F4,3)>0,3,IF(COUNTIF(C4:F4,4)>0,4,IF(COUNTIF(C4:F4,5)>0,5,"No Answer")))))

    I'm sure there is probably a more elegant (simpler) way of doing this.

  4. Ali Zaib says:

    Too nice sharing

  5. Donnie says:

    Im am looking for something simple and I know im over looking it. Using the IFs nestled. I want to type any number in one box, if that number is equal to or greater than 10 i want the output to be five, if it is equal to or less than 24.99 i want it to output nothing. I can get both parts to work but nestled together it wont work correctly

    Thank you

  6. Jhon says:

    I have a data set that lists scores. the data set has three columns
    win/loss sc1 sc2
    L 100 90
    W 101 98
    If the result is "L" i need to transpose the sc1 and sc2...???

  7. Tony R says:

    Trying to write the following function in excel. I have tried many combinations and just cant seem to get it to work
    If A1>600, and A2500, and A2400, and A2<150, then A3=2

    This would all be one long formula with a total of 21 possible combinations which would all change the value of the A3 cell. If anyone can help I would really appreciate it.

  8. Gashakamba says:

    This is cool indeed!
    The Switch....Case statement is a staple in other programming languages; it was about time to have this important tool in Excel as well.

    Thanks for letting the world know about this good news!

  9. Donn Treece says:

    I have the following formula, but Excel tells me it isn't valid. How do I fix this one?
    =IF(ORD19>0,ORD19717,ORD192254,ORd196958,ORD1913317, ORD1919921,ORD1935008,ORD1939454),J27

    It is supposed to be a tax table with the table from cells H20 to J27. The formulas in the J column work well individually, but I cannot get them to auto-figure in cell C17

  10. Donn Treece says:

    New Formula, New Table, works great until the last: Anything over 39454 produces an N/A error.
    =IFS(B17<0,C7,B17<716,C8,B17<2253,C9,B17<6957,C10,B17<13316,C11,B17<19920,C12,B17<35007,C13,B17<39453,C14)

  11. pappu kumar ram says:

    very nice formula

  12. Col says:

    Having issues with the true part of this formula - results are showing #n/a instead of "HP"...
    =SWITCH(F2, "998", "HI", "999", "HI", "485", "Ben", TRUE(), "HP")

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard