Excel IFS function

How to use the Excel IFS function

Description

The Excel IFS function is a logical function in Excel that allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result. IFS is a powerful alternative to nested IF statements as it is shorter, easier to read, and has a higher limit for number of conditions to check.

Syntax

IFS( logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

Arguments

logical_test1 – The first condition that you want to test

value_if_true1 – The result you want to be returned if the logical_test1 result is TRUE

logical_test2 – [Optional] The second condition that you want to test

value_if_true2 – [Optional] The result you want to be returned if the logical_test2 result is TRUE

Return value

Value that corresponds to the first TRUE result

How to use the IFS function in Excel

IFS is a function to test multiple conditions and return a value that corresponds to the first TRUE result. It eliminates the need to use nested IF functions for checking multiple conditions and allows you to produce formulas with many arguments that are easy to read.

The arguments for the IFS function are entered incrementally and pairwise as test/ value pairs. Each logical test returns either TRUE or FALSE, and in case it returns TRUE the respective value is returned

In the above example, we want to assign the correct salary bonus for each employee. As there multiple bonus levels, we perform multiple logical tests in a row until the correct bonus level is found. The IFS statement used above looks like this:

= IFS(C6>90,15000, C6>70,7000, C6>50,3000, C6>30,1000, C6<30,0)

Note, that we explicitly covered the last case of C6 < 30. We did that as the IFS function does not allow to define a custom return value if none of the logical tests evaluates TRUE (like we are used to from the IF function). For the IFS function we have to explicitly define this alternative conditions. Otherwise, if none of the logical tests in the IFS function evaluates TRUE, a #N/A error is returned.

In case you don’t want to explicitly define the condition that covers all other cases, we recommend to embed the IFS function in a IFNA function. IFNA catches the #N/A error in case none of the conditions in the IFS statement are TRUE and allows you define a default return value without explicitly defining the respective condition. For the above example, the adjusted formula would look like this:

= IFNA(IFS(C6>90,15000, C6>70,7000, C6>50,3000, C6>30,1000) ,0)

Alternatively, you can also enter TRUE for the last logical test, and then a value to return as a default if all other logical tests evaluate FALSE:

= IFS(C6>90,15000, C6>70,7000, C6>50,3000, C6>30,1000, TRUE,0)

However, we think this version is less intuitive to understand for a third person.

Logical Operators

Here is an overview of all logical operators you can use to construct the logical tests inside of IFS:

Logical Operator
Meaning
Example
=
equal to
A1 = C1
<>
not equal to
A1 <> C1
>
greater than
A1 > C1
<
smaller than
A1 < C1
>=
greater than or equal to
A1 >= C1
<=
smaller than or equal to
A1 <= C1

Alternative functions to replace nested IF statements

Even though the IFS function is a good alternative for nested IF statements, we recommend to also take a look at the following functions if you want to multiple, mutually exclusive conditions:

AND formula examples

Related Tutorials

Related Functions

How to use the Excel SWITCH function

Excel SWITCH function

The Excel SWITCH compares one value against a list of values, and returns a result value corresponding to the first match.

How to use the Excel IFNA function

Excel IFNA function

The Excel IFNA function is a logical function to trap and handle the #N/A error type in a formula. It returns a custom result for a #N/A error.

How to use the Excel IF function

Excel IF function

The Excel IF function allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result.

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email