Use of IFS function in Excel/ Spreadsheet

This article describes the formula syntax and usage of IFS function in Excel/ Spreadsheet

Before the start, do have a look on “What’s new in Excel 2019 for Windows“

Description of IFS Function

The Excel function IFS runs multiple tests and returns 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.

The syntax of IFS Function in Excel

=IFS (test1, value1, [test2, value2], …)

Argument	Description
test1 (required)	First logical test
value1 (required)	Result when test1 is TRUE
[test2, value2] (optional)	Second test/value pair

Remarks

Use the IFS to test multiple conditions and return a value corresponding to the first TRUE result. Unlike the IF function, IFS allows you to test more than one condition without nesting. This makes formulas with many conditions easier to read.

Arguments are entered in test/value pairs. Each test (condition) represents a logical test that returns TRUE or FALSE, and each value is associated with the previous test. A value is returned by IFS only when its test returns TRUE, and the first test with a TRUE result “wins”. The IFS function supports up to 127 conditions.

Examples

Example 1

In the example shown, the formula in E5, copied down, is:

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

Example 2

To assign “Poor” to scores below 50, “Average” to scores between 50 and 75, and “Good” to scores over 75, you can configure IFS like this:

=IFS(A1<50,"Poor",A1<=75,"Average",A1>75,"Good")

Note: The IFS is new in Excel 2016 via Office 365.

Notes:

• There is no way to set a default if all tests return FALSE (i.e. a value if false). Instead, enter TRUE for the last test, and then a value to return as a default value if FALSE.
• All logical tests must return TRUE or FALSE. Other results will case IFS to return a #VALUE! error.
• If no logical tests return TRUE, IFS returns the #N/A error.

Read Also: 10 Essential Excel Formulas Every Office Pro Should Know

Related: SWITCH function in Excel

If you have any doubt, feel free to contact.