Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016

Learn about 2 of the exciting new functions released as part of the Excel 2016 January update

UPDATE: 29th Feb 2016

Since writing this, it’s occurred to me that whilst these functions are available to o365 Personal/Home accounts, they’ve not yet made it onto the Business accounts, which tend to follow a little later (to allow bugs to be ironed out, etc)

If you’re trying to use these, but nothing is happening, sit tight, and hopefully they’ll be available in the coming weeks

Two of the exciting new features released in the January 2016 update of Office 2016, are the IFS() and SWITCH() functions. It’s important to note that only those with an Office 365 subscription will see these functions, and not for those with a fixed-purchase license.

These related functions are going to be very useful for those of you used to using multiple nested IF functions to get your result, as they allow you to test for multiple conditions in one statement.

The concept of a switch statement won’t be alien to everyone – it’s used quite widely in programming languages, and if you’ve been using a more recent version on Excel for a while now, you would probably be able to guess what role IFS play

So what is the difference between the two?

This particular incarnation of SWITCH is fairly tame – they’ve kept its scope fairly narrow, allowing you to test multiple conditions against a single test value. On the other hand, IFS maintains the full flexibility of IF, but allowing you to test multiple conditions

However, I can see SWITCH being an invaluable function – exactly because of its narrow scope.

There are some cases where you can pick between the two, cases where one is a clear better option over the other, and cases where you only really have one option

So here are a few examples;

In this set of examples, I have month numbers 1-12, and want to build some additional fields for them, starting with the name of the month.

Historically, you’d most likely put the month names in a different table, and perform a VLOOKUP. In this simplistic example, that remains a good option, but let’s explore the alternatives.

Using a nested IF statement would never have been a great solution to this, and with good reason. It’s pretty horrific – and until more recently, would not have been possible given there was a limit of 7 nested IF statements.

=IF(A3=1,"January",IF(A3=2,"February",IF(A3=3,"March",IF(A3=4,"April",IF(A3=5,"May",IF(A3=6,"June",IF(A3=7,"July",IF(A3=8,"August",IF(A3=9,"September",IF(A3=10,"October",IF(A3=11,"November",IF(A3=12,"December","Invalid Month"))))))))))))

But, it does the job.

Using IFS, we can continuously test conditions with different results, to get the result in a far simpler statement

=IFS(A3=1,"January",A3=2,"February",A3=3,"March",A3=4,"April",A3=5,"May",A3=6,"June",A3=7,"July",A3=8,"August",A3=9,"September",A3=10,"October",A3=11,"November",A3=12,"December")

As you can see, we’re testing the cell 12 times, each time with a different result based on the value. A good approach, but a little repetitive testing the condition 12 times

This type of requirement is where SWITCH will really stand out;

=SWITCH(A3,1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")

Here, we’re defining the value we want to test once, at the start. We then go through what we want to do with any given value.

Because we only test once, a SWITCH function will only be useful where you want to test a single value but for multiple conditions.

And, you can of course use the same result for more than one value – here I’m assigning the year Quarter Value, first using IFS, then using SWITCH

=IFS(OR(A4=1,A4=2,A4=3),"Q1",OR(A4=4,A4=5,A4=6),"Q2",OR(A4=7,A4=8,A4=9),"Q3",OR(A4=10,A4=11,A4=12),"Q4")

=SWITCH(A4,1,"Q1",2,"Q1",3,"Q1",4,"Q2",5,"Q2",6,"Q2",7,"Q3",8,"Q3",8,"Q3",9,"Q3",10,"Q4",11,"Q4",12,"Q4")

Neither approach here is a stand-out ‘winner’ – it would have been nice if SWITCH allowed us to give a group of values to assign to a single result, but could not find a way to do this

(ie, something like this…)

=SWITCH(A3,or(1,2,3),"Q1",or(4,5,6),"Q2",or(7,8,9),"Q3",or(10,11,12),"Q4")

However, what SWITCH does allow you to do is assign an ‘Else’ or Default value.

In this formula, I want to set the winter months (1,2,12) a closed, and the rest of the year as Open

=SWITCH(A3,1,"Closed",2,"Closed",12,"Closed","Open")

It’s not possible to set a default value with IFS, though you can of course quite easily achieve this effect.

SWITCH also does not allow you to test for ranges, such as GREATER THAN or LESS THAN – it must be an exact value match.

On the other hand, IFS allows you to do this in the same way you could with IF

Going back to the Quarter identifiers;

=IFS(A3<=3,"Q1",A3<=6,"Q2",A3<=9,"Q3",A3<=12,"Q4")

A further limitation I briefly pointed out before, is that SWITCH can only test against one input value. IFS however can test completely different cells for each part – so if you wanted a statement that combines month and day of the week, you would want to use IFS rather than SWITCH

So there you have it – using the new IFS() and SWITCH() functions gives us some powerful new ways to build conditional statements

1 comment