IFS

Excel Functions

Test multiple conditions, return first true

Syntax

EXCEL

=IFS(test1, value1, [test2], ...)

Arguments

Parameter	Description
`test1`	First logical test.
`value1`	Result when test1 is TRUE.
`test2`	[optional] Second test/value pair.

Return Value

Value corresponding with first TRUE result

Details

The IFs function evaluates multiple expressions and returns a result that corresponds to the first TRUE result. You can use the IFS function when you want a self-contained formula to test multiple conditions at the same time without nesting multiple IF statements. Formulas based on IFS are shorter and easier to read and write. Conditions are provided to the IFS function as test/value pairs, and IFS can handle up to 127 conditions. Each test represents a logical test that returns TRUE or FALSE, and the value that follows will be returned when the test returns TRUE. In the event that more than one condition returns TRUE, the value corresponding to the first TRUE result is returned. For this reason, it is important to consider the order in which conditions appear. An IFS formula with 3 tests

Examples

Structure

An IFS formula with 3 tests can be visualized like this:

EXCEL

=IFS(
test1,value1 // pair 1
test2,value2 // pair 2
test3,value3 // pair 3
)

Example #1 - grades, lowest to highest

In the example shown below, the IFS function is used to assign a grade based on a score. The formula in E5, copied down, is:

EXCEL

=IFS(C5<60,"F",C5<70,"D",C5<80,"C",C5<90,"B",C5>=90,"A")

Example #2 - rating, highest to lowest

In a simple rating system, a score of 3 or greater is "Good", a score between 2 and 3 is "Average", and anything below 2 is "Poor". To assign these va

EXCEL

=IFS(A1>=3,"Good",A1>=2,"Average",A1<2,"Poor")

Example #3 - default value

In the example below, a status code of 100 is "OK", a code of 200 is "Warning", and a code of 300 is "Error". Any other code value is invalid, so TRUE

EXCEL

=IFS(A1=100,"OK",A1=200,"Warning",A1=300,"Error",TRUE,"Invalid")

IFS

Syntax

Arguments

Return Value

Details

Examples

See Also