You can add the IFS Function to Excel 2016, 2013, 2010, and 2007

Excel 365 or Excel 2019 introduced a new function called IFS. You can add an IFS function in Excel 2016, 2013 or your copy of Excel 2010, or 2007 with the Excel PowerUps add-in.

This IFS function in Excel 2016 (or earlier) allows you to specify a series of conditions easily in a single function without having to nest several IF functions. For example:

=IF([test],
IF([test2],
IF([test3],
IF([test4],[value4_test4_true],[value4_test4_false]),
[value3_test3_false]),
[value2_test2_false]),
[value_test_false])

Each condition of the IFS function is followed by the value to be returned if the condition is true. The value returned will be for the first condition that is true.

You can also specify a default value to be returned if none of the values matches the expression. Do this by making the last condition be always true (1=1, or true() ).

If you have ever have to fight keeping track of nested IF statements, you’ll definitely appreciate the simplicity of the IFS function.

Example of the IFS function in Excel 2016

This is an example of the IFS function in Excel 2063 (or Excel 2013 or 2010 or 2007). You can see the difference between using IFS and accomplishing the same task with nested IF statements. You can imagine, as the number of options may increase, how complicated it would be to track the function nesting in Excel.

First, the IFS Function in Excel

Let’s say you want to determine the respective quartile based on a result value. In the example below, you can see returning a value between 1 and 4 based on the result. In a more complex set of nested IF statements the IFS function will be much easier to read and debug if needed.

Next, Using Nested IF Functions Instead

Here is the same function written without the IFS function. You can imagine how unwieldy this would become with more values to keep track of. Getting all of the close parentheses to match up can be a chore.