Excel IFS Function

What is the Excel IFS Function?

Excel IFS Function is an improvement of multiple IF statements. No need nested IF function to analyze more than two criteria, one IFS function is enough but with many logical_test.

Unfortunately, this function is only available for those who subscribe to Office 365. For those who have no Office 365 subscription, please use multiple IF statements or other IFS function alternatives.

IFS Syntax

IFS(logical_test1, value_if_true1
, [logical_test2, value_if_true2]
, [logical_test3, value_if_true3]
, …)

logical_test1 (required), the first condition to be analyzed, whether in accordance with the criteria specified. The result is TRUE / FALSE.

value_if_true1, the returned value if logical_test1 returns a TRUE value

Notes:

• The IFS function able to analyze up to 127 criteria, of course, this is an improvement, nested IF statement only able to accommodate up to 64 IF functions in a formula (before Excel 2007 even only 7 IF functions).
• IFS function has no value_if_false argument, meaning if there are six criteria, then there are six logical_test, in contrast to nested IF function if there are six criteria then there are only five logical_test.
• If no logical_test returns a TRUE value, then the IFS function returns a #N/A error.

IFS Example

What is the weight category of each name based on the BMI value? Can the IFS function answer this question?

There are six weight category according to BMI value.

Here is a formula to answer the questions above.

=IFS(D2<18.5,"Underweight"
,D2<25,"Normal"
,D2<30,"Overweight"
,D2<35,"Class I Obesity"
,D2<40,"Class II Obesity"
,TRUE,"Class III Obesity")

logical_test6 filled with TRUE as a replacement for value_if_false argument in nested IF statements, value_if_true6 will be the result of IFS function if logical_test1 until logical_test5 return FALSE.

Here is the step by step on how to use IFS function.

Another Alternative for IFS Function

Another IFS function alternative is a VLOOKUP function. The following VLOOKUP function able to replace IFS functions.

=VLOOKUP(D12,$A$3:$C$8,3,TRUE)

Another Logical Function