We’ve previously covered how we can nest one or more IF functions inside the IF function.
This enables us to perform multiple different actions depending on the tested condition, but it can also make the whole formula longer than necessary and hard to read/understand/correct.
The IFS function can take the place of multiple nested IF statements and is, in principle, easier to read.
The syntax of the IFS function is as follows:
= IFS ( logical_test ; value_if_true ; logical_test ; value_if_true ; … )
The IFS function checks whether conditions are met and returns a value that corresponds to the first TRUE condition. In a sense, the first test to return TRUE “wins”.
In the following example, we can see the application of both the IFS function and multiple nested IF statements used in order to perform the same task:
Note the additional logical test used in the IFS function.
The IFS function doesn’t support a default result, i.e., there is no such thing as value_if_all_false.
If no TRUE conditions are found, a #N/A error is returned.
If a logical test returns an error and there are no TRUE returns in any of the previous logical tests, the IFS function will also return an error.
In principle, any value returned by a logical test other than TRUE or FALSE should result in the IFS function returning an error, but this is not necessarily true in all versions of Excel.
In the following example, we can see the #N/A error returned by the IFS function:
Given that there is no default result, if we can’t cover all eventualities in logical tests (common when dealing with text data in Excel), and it is also problematic to use functions such as the IFERROR function or the IFNA function in order to create a default result (logical tests can also result in errors), the only sensible way to create a default result is by adding an additional logical test that will always return TRUE.
We can accomplish this by nesting the TRUE function as the final logical test:
If readability is the goal, we can also add line breaks to an IFS formula:
Dig deeper:
One thought on “IFS function”