How to Use IFS Function in Excel – 5 Examples

Using the IFS function in Excel, you can dramatically elevate your Excel skills to the next level. The IFS function is a single function that makes it much easier to deal with multiple logical conditions.

But what exactly is the IFS function in Excel, and how is it better than the standard IF function? Continue reading to discover more about the IFS function and to get rid of a long, complex, nested function utilizing the IFS function in Excel.

Purpose of the IFS Function

The IFS function in Excel checks the values against predefined conditions. If the first condition is satisfied, the formula terminates and outputs a result. If the first condition is not satisfied, the formula will jump to the next condition, and so on until a condition is satisfied.

Syntax of the IFS Function

=IFS(logic1, return1,logic2,return2,...........)

Arguments of the IFS Function

logic1: states the first condition you want to evaluate

return1: specifies the value to be returned if logic1 is satisfied.

logic2: specifies the second condition to be evaluated. (optional)

return2: specifies the value to be returned if logic2 is satisfied. (optional)

Similarly, up to 127 logics can be added to the arguments of the IFS function.

5 Examples to Use of the IFS Function in Excel

Example #1: Creating a Grade sheet Using the IFS function

In the datasheet below, the student names, and scores are given.

We want to create a grade sheet maintaining the standard grade mentioned in the datasheet.

=IFS(C3<30,"F",C3<50,"Passed",C3<60,"B",C3<70,"A-",C3<80,"A",C3>=80,"A+")

In the output cell D3, enter the above formula and press Enter.

Then, in column D, AutoFill the remaining cells.

Explanation:

• If the value in cell C3 is smaller than 30, then the IFS function returns F. The first condition is FALSE for John. So, the IFS function will jump to the next condition until any one of the conditions is satisfied.
• But for George, the first condition is satisfied and so the IFS function is terminated.

Example #2: Using the IFS Function to Set a Default Value

In Example #1, we wrote the logic in the formula from the lowest to the highest order.

Now we will solve the same problem from the highest to the lowest order and set a default value.

=IFS(C3>=80,"A+",C3>=70,"A",C3>=60,"A-",C3>=50,"B",C3>=30,"Passed",TRUE,"Failed")

If all of the conditions in the formula above return FALSE, the IFS function considers ”Failed” to be the default value.

Explanation:

Here, if any student gets a number less than 30, first five condition is not satisfied and TRUE will be the final check. IFS function will return ‘’Failed’’

Let’s evaluate the formula for Don,

=IFS(FALSE,"A+",FALSE,"A",FALSE,"A-"FALSE0,"B",FALSE,"Passed",TRUE,"Failed")

=Failed

Example #3: IFS Function vs nested IF Function

To understand the benefit of the IFS function over the nested IF function, you should look over the syntax of the IF function.

Syntax of the IF Function:

=IF(logic1,return1,return2)

The argument of the IF Function:

logic1: conditional expression, you want to evaluate

return1: the text or logical expression you want to see if logic1 is satisfied.

return1: the text or logical expression will be returned if  logic1 is not satisfied.

Now, try an example, If we want to create the same grade sheet using the IF formula, we have to be more laborious.

 Also, the nested IF formula looks so clumsy.

=IF(C4<30,"F",IF(C4<50,"Passed",IF(C4<60,"B",IF(C4<70,"A-",IF(C4<80,"A",IF(C4>=80,"A+"))))))

Example #4: Using IFS with nested YEAR function

Suppose a company needs to check the validity of its products. said that its validity expires two years after its production.

So the formula will be:

=IFS(YEAR(TODAY())-B3<=2,"Expired",TRUE,"Ok")

Explanation:

• The YEAR function returns the year based on a given date. We’ll transmit today’s date to the YEAR function via the TODAY function.
• YEAR(TODAY()) returns the current year.
• If the difference between the production year and the current year is greater than 2, the IFS function will return “Expired”, otherwise “Ok”

Example #5: Using IFS with the nested TEXT function

Consider the following example, which includes another function in the position of returns in the syntax of the IFS function.

We should know before starting the example,

Syntax of TEXT function:

=TEXT(value, format)

The argument of TEXT function

value: the numerical value we want to convert into the desired format.

format: indicates the format into which the value will be converted. See the following table,

Now, Let’s jump to the example,

Suppose, we want to convert the Bytes data into Kb, MB, GB

=IFS(B3>=1024 * 1024 * 1024, TEXT(B3/(1024 * 1024 * 1024), "0.0") & " GB", B3>=1024 * 1024, TEXT(B3/(1024 * 1024), "0.0") & " Mb", B3>=1024, TEXT(B3/1024, "0.0") & " Kb")

Things to remember on IFS Function

• You can add up to 127 conditions in the syntax of the IFS function.
• IFS Function is a recent gift by Microsoft, it is available only in Excel version 2019 and Office 365
• The IFS function doesn’t have any built-in default value to return, so if no condition is met, it will return the #N/A error.
• If the conditions in the argument return anything other than TRUE or FALSE, the IFS function returns #VALUE! Error.

IFS Function versus SWITCH Function

The SWITCH and the IFS are both available in Microsoft Excel 2019 and Office 365. Besides, both are applied to handle multiple condition sets. The SWITCH function only supports exact matching. You can’t provide any logical expression (<, >, or =) to test. The SWITCH function, on the other hand, has the advantage of allowing you to specify multiple values to return if you want to see different values corresponding to the first condition. There is no need to repeat the SWITCH function.

Although logical expressions are supported by the IFS function, you can only specify one value to return if the condition is met.

Conclusion

Obviously, you should be an expert on using the IFS function in Excel after reading the article. If you have any questions about the IFS function, you can comment below.