Excel IFS Function

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.