The Excel IFS Function

Related Functions:

Function Description

The Excel IFS function tests a number of supplied conditions and returns the result corresponding to the first condition that evaluates to TRUE. If none of the supplied conditions evaluate to TRUE, the function returns the #N/A error.

The syntax of the IFS function is:

IFS( logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3], ... )

where the arguments are as follows:

logical_test1 [logical_test2], ...	-	The conditions that are to be tested and evaluated as either TRUE or FALSE.
value_if_true1 [value_if_true2], ...	-	The results that are to be returned if the corresponding logical_test evaluates to FALSE.

Note:

You can enter up to 127 pairs of logical tests and values into the Excel Ifs function.
If you want the Ifs function to return a default value (instead of an error), in the case where none of the conditions evaluate to TRUE, it is advised that you add a final condition that will always evaluate to TRUE (e.g. the logical value TRUE). An example of this is given below.
The Ifs function was introduced in Excel 2019 and so is not available in earlier versions of Excel.

Excel Ifs Function Examples

Ifs Function Example 1

The following Ifs function examples return:

The value in column A divided by the value in column B if the value in column B is greater than 1.
The value in column A multiplied by -1 and then divided by the value in column B if the value in column B less than -1.
The value 0 otherwise.

Formulas:

	A	B	C
1	5	4	=IFS( B1>1, A1/B1, B1<-1, -A1/B1, TRUE, 0 )	- returns the value 1.25
2	5	-2	=IFS( B2>1, A2/B2, B2<-1, -A2/B2, TRUE, 0 )	- returns the value 2.5
3	5	0	=IFS( B3>1, A3/B3, B3<-1, -A3/B3, TRUE, 0 )	- returns the value 0

Results:

	A	B	C
1	5	4	1.25
2	5	-2	2.5
3	5	0	0

Note that the final condition of the above Ifs functions is simply "TRUE". As this always evaluates to TRUE, the corresponding value, 0, is returned whenever neither of the prior conditions evaluate to TRUE (as in cell C3).

If we had not included this final condition, the example in cell C3 would have returned the #N/A error.

Ifs Function Example 2 - Simplifying Nested If Functions

The Ifs function greatly simplifies what previously might have been multiple nested If functions.

For example, prior to Excel 2019, you might have used the following nested if function:

=IF(A1=1, "Sun", IF(A1=2, "Mon", IF(A1=3, "Tue", IF(A1=4, "Wed", IF(A1=5, "Thu", IF(A1=6, "Fri", IF(A1=7, "Sat", "")))))))

In Excel 2019 or Excel 365, this can be replaced by the following single call to the Ifs function which avoids the repeated brackets, and is therefore easier to type and less prone to errors:

=IFS( A1=1, "Sun", A1=2, "Mon", A1=3, "Tues", A1=4, "Wed", A1=5, "Thu", A1=6, "Fri", A1=7, "Sat" )

For further details and examples of the Excel Ifs function, see the Microsoft Office website.

Ifs Function Errors

If you get an error from the Excel Ifs function, this is likely to be one of the following:

Common Errors

#N/A	-	Occurs if none of the supplied logical_tests evaluate to TRUE.
#VALUE!	-	Occurs if one or more of the supplied logical_tests returns any value other than TRUE or FALSE.
#NAME?	-	Occurs if you are using an older version of Excel (pre-2019), that does not support the Ifs function.

Return to the Excel Logical Functions Page
Return to the List of All Built-In Excel Functions