IFS

Using the Excel IFS Function

Summary

Excel's IFS function is used to take the place of multiple nested IF statements when checking if one or more conditions have been met. The function tests multiple conditions and returns the value of the first TRUE condition. Using IFS provides for shorter and easier to read formulas.

Syntax

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Syntax Breakdown

logical_test1
The logical_test1 argument is required. It should be a condition that evaluates to TRUE or FALSE.

value_if_true1
The value that should be returned if logical_test1 evaluates to TRUE. This argument can be blank.

logical_test2
The logical_test2 argument and subsequent logical_test arguments are optional. The IFS function can handle up to 127 different conditions.


Usage Notes

The IFS function is useful when testing multiple conditions and is much easier to read than nested IF statements that would be required if using IF by itself. Each condition entered into the IFS function should evaluate to either TRUE or FALSE. IFS returns the value of the first TRUE condition and will continue to execute the logical tests until a TRUE condition is found.

It is important to pay close attention to the logical tests you enter in the formula. As more tests are entered it is critical to enter them in the correct order and to ensure that updates and changes are made with the same level of care.

#N/A Error
Should the IFS function execute all logical tests with none proving TRUE, then the #N/A error will be returned.

Excel IFS #NA Error

To avoid the #N/A error it is possible to specify a default result by entering TRUE as the final logical test and specifying the return value. If all other logical tests evaluate to FALSE, the final value will be TRUE and return the value that you have specified as opposed to the #N/A error.

IFS #NA Error Solution

#VALUE! Error
The IFS function only accepts logical tests that evaluate to either TRUE or FALSE. The function returns the #VALUE! error if an argument is entered that cannot be evaluated to TRUE or FALSE.