IFS

What it does

IFS replaces a chain of IF functions nested inside each other. Write the tests from the strictest to the loosest: Excel stops at the first one that is TRUE.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

logical_test

A test that gives TRUE or FALSE.

value_if_true

What to return if this is the first true test.

Example

Grades: 90 or more is A, 80 or more is B, 70 or more is C, the rest is D. What does a score of 84 get?

The formula in B2:

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"D")

Result: B

84>=90 is FALSE, so Excel moves on. 84>=80 is TRUE, so the answer is B and the later tests are never checked. The last test, TRUE, catches every score below 70.

Common mistakes

• Tests in the wrong order. If 70 or more comes first, a score of 95 stops there and gets C.
• No catch-all at the end. When no test is TRUE, IFS returns #N/A; end with TRUE and a default result.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• IFS: many conditions in orderModule 4 · Logic

More from this module

• IF
• SWITCH
• AND
• OR
• NOT
• XOR
• ISNUMBER
• ISTEXT
• ISBLANK
• ISERROR
• IFERROR

All functions