Excel Essentials: Simplifying Complex Decisions with the Advanced IFS Statement Tutorial

Quick Links

• Use the IFS Function in Excel

If you’re familiar with using the IF function in Excel, then you might be ready to check out the IFS function. With it, you can test multiple conditions at once, instead of using nested IF statements.

Using IFS, you can test up to 127 conditions in a single Excel formula. Although this number of tests is probably much more than you need, the function is ideal for checking several 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 for each

    `test`

argument and the value to return if the test is true for each

    `true`

argument.

Let’s look at a few basic examples.

Return Cell Reference Values

Here, the condition looks at the ID number in cell C2. When the formula finds the matching condition, 1 through 5, it returns the corresponding 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 4, return the value in A5, and if it’s 5, return the value in A6.

If you were to use 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 obtain the same result using either option, the formula for the IFS function is a little less confusing and doesn’t take as much time to assemble .

Related: The Basics of Structuring Formulas in Microsoft Excel

Click here

Return Numbers

For another IFS example, we’ll apply bonuses to our salespeople based on their sales totals. Here’s the formula:

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

To break down this formula, if the total 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.

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

To compare again, here is what the formula looks like as 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 eliminate the need to repeatedly type IF with an opening parenthesis and remember the correct number of closing parentheses at the end.

Return Text Values

In this next example, we have a list of employees who haven’t finished their training. We’ll display a text result based on the percent complete in cell B2. Note that the text values must be in quotes.

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

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

For one more comparison, here is what the nested IF statement looks like to obtain the same result:

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

It can be especially difficult to troubleshoot formulas errors in lengthy statements or those that needs extra care like including quotation marks. This is just one more reason to consider using the IFS function instead of a nested IF formula.

As Microsoft explains :

Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then 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 hard to maintain. This is another consideration for using the IFS function, especially if you’re working on your spreadsheet with others .

When you want to test several conditions for a cell, or even a cell range, consider using the IFS function. For more, learn how to use the other logical functions in Excel like AND, OR, and XOR .

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |

Also read: