How to Use the IFS Function in Microsoft Excel

If you are familiar with using the IF function in Excel, you might be ready to try the IFS function. This allows you to test multiple conditions at once instead of using nested IF statements.

With IFS you can test up to 127 conditions in a single Excel formula. Although this number of tests is probably far more than you need, the feature is ideal for checking multiple conditions. The formula then returns the value you specify for the first true condition in the statement.

Use the IFS function in Excel

The syntax for the function is IFS(test1, if_true1, test2, if_true2,...) where you enter the condition test argument and the value to return if the test is true for both true Fight.

Let’s look at a few basic examples.

Return cell reference values

Here the condition looks at the ID number in cell C2. If the formula finds the matching condition 1 through 5, it returns the matching name.

=IFS(C2=1,A2,C2=2,A3,C2=3,A4,C2=4,A5,C2=5,A6)

To break down the formula: if the value in cell C2 is 1, return the value in A2, if it’s 2, return the value in A3, if it’s 3, return the value in A4, if it’s is 4, return the value in A5, and if it’s 5, return the value in A6.

If you used a nested IF statement instead of the IFS function, your formula would look like this:

=IF(C2=1,A2,IF(C2=2,A3,IF(C2=3,A4,IF(C2=4, A5,IF(C2=5,A6)))))

While you get the same result with either option, the formula for the IFS function is a little less confusing and doesn’t take as much time to put together.

TIED TOGETHER: The basics of structuring formulas in Microsoft Excel

return numbers

As another IFS example, we apply bonuses to our sellers based on their total sales. Here is the formula:

=IFS(F2>100000,1000,F2>75000,750,F2>50000,500)

To break this formula down: if the sum in cell F2 is greater than 100,000, return 1,000, if it’s greater than 75,000, return 750, and if it’s greater than 50,000, return 500.

Tip: You can use the fill handle to copy the same IFS formula to adjacent cells.

To compare again, the formula looks like a nested IF statement instead:

=IF(F2>100000,1000,IF(F2>75000,750,IF(F2>50000,500)))

By using the formula for the IFS function, you avoid the need to repeatedly type IF with an opening bracket and remember the correct number of closing brackets at the end.

return text values

In this next example, we have a list of employees who have not yet completed their education. We display a text result based on the percentage complete in cell B2. Note that text values ​​must be enclosed in quotes.

=IFS(B2<50,"Less than half",B2=50,"Half",B2>50,"More than half")

To break this formula down: if the value in cell B2 is less than 50, return “Less than half”, if it’s equal to 50, return “Half”, and if it’s greater than 50, return ” More than half”.

For another comparison, here’s what the nested IF statement looks like to achieve the same result:

=IF(B2<50,"Less than half",IF(B2=50,"Half",IF(B2>50,"More than half")))

It can be particularly difficult to fix formula errors in long statements or those that require special care, such as B. inserting quotation marks. This is just another reason to consider using the IFS function instead of a nested IF formula.

As Microsoft explains:

Multiple IF statements require a lot of thought to be properly constructed and to ensure their logic can properly evaluate each condition to the end. If you don’t nest your formula 100% accurately, it might work 75% of the time but return unexpected results 25% of the time.

Microsoft goes on to say that nested IF statements can be difficult to maintain. This is another consideration for using the IFS feature, especially if you are collaborating on your spreadsheet.

TIED TOGETHER: How to fix common formula errors in Microsoft Excel

If you want to test multiple conditions for a cell or even a range of cells, you should consider using the IFS function. Learn more about how to use the other logical functions in Excel like AND, OR, and XOR.