This tutorial is applicable to Excel 2016* onwards.
In Excel 2016 there are a load of new functions, one of which is the IFS Function. Given last week’s topic, ‘When to say no to Excel Nested IFs’ I thought it was a good time to introduce the Excel IFS function.
The IFS function removes the need to nest IF functions.
IFS Syntax:
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, logical_test3, value_if_true3,….)
You can nest up to 127 logical tests, but that doesn’t mean you should.
Excel IFS Function Example
In column B we can use the IFS function to return the State for each City in column A. I’ve added line breaks in the formula bar in the image below so it’s easier to read:
Here is the IFS formula without the line breaks:
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT",[@City]="Perth","WA", TRUE,"Not Found")
In earlier versions of Excel we’d use a nested IF like this:
=IF([@City]="Sydney","NSW", IF([@City]="Melbourne","VIC", IF([@City]="Adelaide","SA", IF([@City]="Brisbane","QLD", IF([@City]="Darwin","NT", IF([@City]="Perth","WA","Not Found"))))))
The benefit of the new IFS function is an easier to build and read formula.
IFS Value if False
You may have noticed that unlike the IF function, with the IFS function there is no ‘value_if_false’ argument. Instead we simply insert a logical test at the end that evaluates to TRUE, or just type in TRUE, and then insert our value_if_false argument.
=IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT",[@City]="Perth","WA",TRUE,"Not Found")
Limitations
The IFS Function is limited to 127 logical tests. However, if your formula uses more than 7 I’d consider an alternative, because this formula will get difficult to maintain and may result in performance issues.
There are more efficient ways to get the same results, as discussed in this tutorial ‘When to say no to Excel Nested IFs’. Even though this article deals with Nested IF’s, the same issues apply with IFS.
IFS in Earlier Versions of Excel
If a user opens an Excel file containing the IFS function in an earlier version of Excel it will still display the result, but the function will be prefixed with _xlfn. Like so:
=_xlfn.IFS([@City]="Sydney","NSW", [@City]="Melbourne","VIC", [@City]="Adelaide","SA", [@City]="Brisbane","QLD", [@City]="Darwin","NT", [@City]="Perth","WA", TRUE,"Not Found")
They can happily work in the file and save it without breaking the formula.
However, should the user edit the cell in a version of Excel that doesn’t have the IFS function the result will be converted to the #NAME? error. Opening it in Excel 2016 again will fix the errors.
Download the Workbook
*Note: not all versions of Excel 2016 will have the new functions. If you purchased a standalone version of Excel 2016 then chances are you got a copy before the new functions were added. Excel 2016 Office 365 users will receive new features as they are released. This is part of Microsoft’s strategy to shift more people to the subscription model.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
David Blumberg says
I have Excel 2010 and the expressions IFS or _XLFN.IFS do not appear when I type into a cell. I gather that is only available 2016. Please confirm>
David
Mynda Treacy says
Hi David,
Correct, IFS is only available in Excel 2016 (the Office 365 version). If you receive a file containing an IFS formula and open it in an earlier version of Excel you will see the function is prefixed with _xlfn.
That’s not to say you can use the IFS Function in Excel 2010 by typing _xlfn.IFS
Kind regards,
Mynda
Col Delane says
Hi Mynda
As you mentioned in your earlier blog about nested IFs, often it was the wrong function to use in the first place. I see the same occurring with IFS. For my mind, IFS is still the wrong function to use for a multiple option (i.e. which City of 6?) but single level (return corresponding State) decision tree like the example you’ve given. As Oz suggests, a lookup style function or CHOOSE would be better suited to this sort of application.
Whilst perhaps not best practice financial modelling, I sometimes nest 2-3 IFs, where there may be two further options if the first test = TRUE, and a completely different option if the first test = FALSE. I don’t see IFS eliminating the occasional need to nest such functions in order to cater for several levels of a decision tree.
Mynda Treacy says
Hi Col,
Great to see you’re not one of the many who nest endless IF’s 🙂
Mynda
Oz du Soleil says
You now my feeling about IFS.
LOL!
That final argument just isn’t intuitive. But you and I agree that too many conditions should lead a person to abandon IF or IFS and think about another solution, like a lookup range with VLOOKUP or INDEX/MATCH.
Mynda Treacy says
🙂 that is the hope.