Conditions are provided to the IFS function as test/value pairs, and IFS can handle up to 127 conditions. Each test represents a logical test that returns TRUE or FALSE, and the value that follows will be returned when the test returns TRUE. In the event that more than one condition returns TRUE, the value corresponding to the first TRUE result is returned. For this reason, it is important to consider the order in which conditions appear.
Structure
An IFS formula with 3 tests can be visualized like this: A value is returned by IFS only when the previous test returns TRUE, and the first test to return TRUE “wins”. For better readability, you can add line breaks to an IFS formula as shown above. Note: the IFS function does not provide an argument for a default value. See Example #3 below for a workaround.
Example #1 - grades, lowest to highest
In the example shown below, the IFS function is used to assign a grade based on a score. The formula in E5, copied down, is: Notice the conditions are entered “in order” to test lower scores first. The grade associated with the first test to return TRUE is returned.
Example #2 - rating, highest to lowest
In a simple rating system, a score of 3 or greater is “Good”, a score between 2 and 3 is “Average”, and anything below 2 is “Poor”. To assign these values with IFS, three conditions are used: Notice in this case conditions are arranged to test higher values first.
Example #3 - default value
The IFS function does not have a built-in default value to use when all conditions are FALSE. However, to provide a default value, you can enter TRUE as a final test, followed by a value to use as a default. In the example below, a status code of 100 is “OK”, a code of 200 is “Warning”, and a code of 300 is “Error”. Any other code value is invalid, so TRUE is provided as the final test, and “Invalid” is provided as a “default” value. When the value in A1 is 100, 200, or 300, IFS will return the messages shown above. When A1 contains any other value (including when A1 is empty) IFS will return “Invalid”. Without this final condition, IFS will return #N/A when a code is not recognized. Note: IFS is a new function available in Excel 365 and Excel 2019.
IFS versus SWITCH
Like the SWITCH function, the IFS function allows you to test more than one condition in a single self-contained formula. Both functions make it easier to write (and read) a formula with many conditions. One advantage of SWITCH over IFS is that the expression appears just once in the function and does not need to be repeated. In addition, SWITCH can accept a default value. However, SWITCH is limited to exact matching. It is not possible to use operators like greater than (>) or less than (<) with the standard syntax. In contrast, the IFS function requires expressions for each condition, so you can use logical operators as needed.
Notes
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
