In this tutorial, you’ll learn about newly introduced Excel IFS function that can be used as an alternative to the nested IF statements. You will also explore the syntax and the use with a few examples. This function is only available in Excel 2016 If you have Office 365 subscription.
The newly added IFS to the logical family by Excel team, allows us to check multiple conditions and returns a value that meets the first TRUE condition. The Excel IFS function can be used in place of nested IF conditions which consumes less time and much easier to read with multiple statements.
You can use IFS function in situations when you want to test one or more than one logical test to return value corresponding to the first matched TRUE condition.
The old method of nesting IF statement is very common and used in circumstances to evaluate multiple conditions to return different type of outcomes. However, the same can become a bit difficult in situations to maintain if the nested statement is not written correctly. Even if one of the supplied argument is entered incorrectly, nested IF formula may not return the correct answer.
IFS (logical_test, value_if_true, [logical_test2, value_if_true2], …)
- logical_test1: It is a first logical test that evaluates to TRUE or FALSE.
- value_if_true1: The second mandatory argument returns the result if logical_test1 evaluates to TRUE. If required, can be empty too.
- logical_test2…logical_test127: An optional logical condition that evaluates to TRUE or FALSE.
- value_if_true2…value_if_true127: The result to be returned if logical_testN evaluates to TRUE. Each value_if_trueN relates to a logical_testN If needed, can be empty.
How to use Excel IFS Function?
Writing IFS function in Excel is so easy and after going through the syntax, I am hoping now you are quite comfortable and ready to explore the use of IFS function in Excel. Let’s take a simple example to have a basic understanding first.
Let’s say you want to assign the grade to the marks obtained by students. You can use IFS function as follow.
=IFS (E2>150, “Excellent”, E2>=100, “Pass”, E2<100, “Fail”)
Based on the above-given example, the function is having 3 criteria to check and then return the value corresponds to the first TRUE condition.
With nested IF, it looks something like this:
=IF (E2>150, “Excellent”, IF(E2>=100, “Pass”, “Fail”) )
Example: IFS with ELSE condition
Suppose there is an employee database provided with salary field and you want to arrange the database in four groups as per their salaries in “L1”, “L2”, “L3” and “L4”.
The IFS function below returns the corresponding value to the logical test when it evaluates to TRUE. However, when none of the logical tests evaluate to TRUE, IFS displays #N/A error which is shown in cell D7 in the following example.
=IFS (C2>1400, “L4”, C2>1200, “L3”, C2>1000, “L2”, C2>800, “L1”)
The #N/A error can be replaced by placing TRUE in the last logical test followed by the value you want to return by value_if_true argument.
=IFS (C2>1400, “L4”, C2>1200, “L3”, C2>1000, “L2”, C2>800, “L1”, TRUE, “Not Found”)
Because function needs TRUE to return the corresponding value argument when none of the previous logical conditions meet the criteria, the last logical test and value pair is executed since TRUE is part of the final logical test that helps to return “Not Found” in place of #N/A error.
Additional Notes: –
- The function is only available in Excel 2016, a part of Office 365 subscriptions.
- You can evaluate up to 127 different conditions.
- When any logical test is supplied without the corresponding value argument, IFS displays the message “You’ve entered too few arguments for this function”.
- IFS returns #N/A error when no TRUE conditions are found.
- #VALUE! error occurs when logical conditions return the value other than TRUE or FALSE.
IF | SUMIFS | AVERAGEIFS | COUNTIFS