Ifs() Function

June 1, 2022
Logic Function

The IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas.

Syntax

The syntax for the IFS function in Microsoft Excel is:

=IFS( condition1, return1 [,condition2, return2] ... [,condition127, return127] )

Parameters or Arguments

condition1, condition2, … condition127

The condition that you want to test. There can be up to 127 conditions entered.

return1, return2, … return127

The value that is returned if the corresponding condition is TRUE. All conditions are evaluated in the order that they are listed so once the function finds a condition that evaluates to TRUE, the IFS function will return the corresponding value and stop processing any further conditions.

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.

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" )

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.

Ifs Function Errors

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

#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.