Excel IFS Function – How To Use

The IFS function in Excel is a logical function that, in simplest terms, applies multiple IF functions. It allows the users to test a value against multiple conditions. The function accepts one or more conditions (or Boolean expressions) and a value to be returned against each condition.

The result returned by the function is the value that corresponds to the first condition, which evaluates to TRUE. IFS function is considered to be an easy and relatively simple alternative to the nested IF statements.

Syntax

The syntax of the IFS function is as follows:

Arguments:

logical_test1 – This is the first condition (or a Boolean expression) that the function needs to evaluate. If this condition is found to be TRUE, the function stops here and does not proceed to the next condition (if any). On the other hand, if the condition evaluates to FALSE, it moves on to compute the next condition (if any).
value_if_true1 – This is the value that is returned if the 'logical_test1' turns out to be TRUE.
[logical_test2] – This is an optional parameter that specifies the second test condition, which the function evaluates when the first condition turns out to be FALSE.
[value_if_true2] – This is also an optional parameter that specifies the value that is returned if the first condition turns out to be FALSE and the second condition turns out to be TRUE.

Important Characteristics of IFS function

• The IFS function can support up to 127 conditions.
• If the conditions used inside the IFS function result in a value other than TRUE or FALSE, #VALUE! error is returned.
• If all the conditions within the IFS function evaluate to FALSE, then the function returns a #N/A error.
• If all the conditions within the function evaluate to FALSE, then a default value can be provided within the formula (we will see this with an example). However, the function does not support this out of the box, and this default value needs to be added with a condition that will always evaluate to TRUE (like 1=1 or TRUE, etc.)

Examples of the IFS function

Let us look at a few examples to understand the IFS function better.

Example 1 – Plain Vanilla Version of IFS Function

Consider a dataset consisting of three columns – Column 1 consists of ‘Student Roll Number’, Column 2 consists of ‘Student Marks’, and Column 3 consists of ‘Remarks’. Now, based on the 'Student Marks' column, we have to assign a specific remark against each student in the data set.

Let's try to populate the 'Remarks' column based on the given conditions using the IFS function. In this case, the formula would be:

Let's try to wrap our heads around the formula and understand what is happening inside –

B2 is the cell containing data that has to be tested for the specified conditions, and the Remarks column needs to be populated based on its value.

The IFS function checks the first condition – if 93<60, which turns out to be FALSE. When this condition is found to be FALSE, it checks the next condition – if 93<=80, which again evaluates to be FALSE. When this condition is also found to be FALSE, it moves on to the next one to check – if 93<=90, this also turns out to be FALSE. Similarly, it checks for all conditions one by one until any one of the conditions evaluates to TRUE.

In this case, the condition 93<=100 yields the result TRUE, and so the value corresponding to it, i.e., "Excellent," is populated in the 'Remarks' column.

This formula returns the required result against each student when we drag down the formula to all the other cells using the fill handle.

Example 2 – IFS Function with a Default Value

Now that this function returns the perfect value for each cell as required, what if the 'logical_test' argument does not match any of the given conditions?

For example, if there is another row that contains Student Marks as 105, there is no test condition in the given function that matches this value. In such a case, the function returns #N/A error.

Let’s look at this below.

IFS function does not provide a built-in way to have a default value to use when all conditions evaluate to FALSE. However, with some clever logic, we can add a default value within the IFS function to handle such cases.

To add a default value, we have to add a condition to the function that always evaluates to TRUE – For example, 1=1, "A" = "A", or simply passing TRUE or 1 as the logic test would do the trick. Any value tied to such a condition would be displayed when no other condition matches.

Therefore, the formula can be modified as follows:

Since the value 105 doesn't fall under the specified conditions, the default value "Out of Range" is displayed.

Note: The default condition should always be the last condition in the IFS function.

IFS vs Nested IF Statements

Before the introduction of the IFS function in MS Excel 2019, we used to have nested IF statements. The formulas using nested IF statements are much complicated when compared to the formulas using the IFS function. Let us try to use a nested IF formula for the same example stated above to understand this distinction.

With nested IF statements, the formula would be –

This formula appears to be complicated using multiple parentheses and IF functions nested within one another, which is not the case with the IFS function. So, clearly, the IFS function is the winner here. It is relatively easier to write and understand when compared to multiple nested IF functions.

Recommended Reading – Excel If Function – How to Use

SWITCH function in Excel

The SWITCH function in Excel is similar to the IFS function. It compares one value (or the result of an expression) against a list of values and returns the result corresponding to the first exact match found. A default value can be specified at the end when no values match the given expression.

Syntax

The syntax of the SWITCH function is as follows:

Arguments:

expression – The expression that is to be compared with other values/expressions.
value1/result1 – SWITCH function compares the data using value/result pairs. The first value/result pair against which the expression has to be compared is given here.
[value2]/[result2] – This is an optional parameter. It is the second value/result pair against which the expression has to be compared when the first comparison turns out to be FALSE.
[default] – This is also an optional parameter. It refers to the default expression that appears when all the other comparisons turn out to be FALSE.

Example of SWITCH function

Let’s look at an example to understand the SWITCH function better.

We have a dataset in the example with three columns "Apple Variety in Stock", "Rating" and "Grade" representing the quality of apples. Based on the rating we have to populate the quality of apples.

• Apples that are rated 5 are "Grade A" apples.
• Apples that are rated 4 are "Grade B" apples.
• Apples that are rated 3 are "Grade C" apples.
• Apples that are rated 2 are "Grade D" apples.
• Apples that are rated 1 are "Grade E" apples.

So, we use the following formula –

In this formula, the data in B3 is the expression that is to be compared against a set of value/result pairs.

The rating 5 is the first value against which data in B3 is compared. If the data in B3 matches 5, "Grade A" is returned as a result. If it turns out to be FALSE, it is compared against the next value, 4. If the data in B3 matches 4, "Grade B" is returned as a result.

If all evaluations turn out to be FALSE, the default value specified is returned. In this case, the default value specified is "Invalid".

Note: Up to 126 values/result pairs can be evaluated using the SWITCH function

Now, let’s see how the IFS function can be used for the same example.

In this case, the formula would be –

The IFS function evaluates each condition and returns the corresponding result. And if no condition is found to be TRUE, the default value "Invalid" is populated in the required column.

IFS vs SWITCH FUNCTION

Both IFS and SWITCH functions are very useful for testing. They evaluate multiple conditions and serve as an easy and quick alternative to the nested IF statements.

One minor edge of SWITCH over IFS is that the expression or the condition to be tested appears just once in the function and does not need to be repeated.

Another key difference between IFS and SWITCH functions is that the SWITCH function computes the exact match of the expression specified in the formula.

With SWITCH, you cannot use the logical operators like greater than (>) or less than (<) in the logic used to decide a match. For that reason, when an exact match is to be returned as a result, SWITCH could be a better option.

However, the IFS function can compute different logical expressions and return a value based on the logical computation. Thus, when the expressions have to be logically evaluated before returning a result, IFS serves as a great option.

That was all about the IFS function in Excel. I hope this tutorial will give you a confidence boost next time while dealing with the IFS function. I will see you soon with another amazing Excel function. Until then, keep Excelling.