How to Use the IFS Function in Excel
(Many Examples)

For sure Microsoft heard you when you were struggling to construct a long nested IF function to test multiple logical criteria and brought you the IFS function instead.

Nested IF is used in Excel to evaluate situations with multiple outcomes. However, as the outcomes increase, writing this function not only becomes clumsy but time-consuming, too.

But no worries – The IFS function available in Microsoft 365, Excel 2021, and Excel 2019 substitutes nested formulas really well 🙌

In this guide, we will explore the IFS function of Microsoft Excel – from syntax to multiple practical applications. So get your free practice workbook for this guide here and stay tuned.

Table of Contents

What is the IFS function

The IFS function in Excel is meant to test more than one criterion. If the first criteria passes the logical test; the IFS function returns the value_if_true and the formula stops.

However, if the first criteria, it then tests the next logical criteria and so on. It is designed to replace the old nested IF formula and is much easier (and simpler) to write and read 📝

Syntax

The syntax of the IFS function looks like below:

• logical_test1 is the first logical test which is a required argument and must be run.
• value_if_true1 is the value to be returned by the IFS function is the first logical test turns true. This is also a required argument.
• logical_test2 is the second logical test to be performed if the first logical test fails. It is an optional argument.
• value_if_true2 is the value to be returned by the IFS function is the second logical test turns true. It is an optional argument

Here onwards, you can specify up to 127 logical tests along with their related value_if_true to the IFS function. However, once you have specified a logical test, say logical_test3, it is mandatory to specify the value_if_true for it, or else Excel will prompt an argument that the supplied arguments are too less.

How about we now jump into seeing examples of the practical application of the IFS function?

Using the IFS function in Excel

We will now look into many examples of using the IFS function in Excel.

Let’s dive in straight 🤿

Example 1

Below is the list of members of a Hotel’s Prestige Club.

The status of these members is determined based on the number of points to their credit.

If a member has 150 points to his credit, he wins the status of a Golden member. If he has 100 points to his credit, he becomes a Golden Member and with 50 points, he stands as a Silver Member 💳

Let’s now populate the status of each member using the IFS function.

Step 1) Begin writing the IFS function as follows:

Step 2) As the first logical test, write if Cell B2 (points) equates to 150.

Step 3) Write the value_if_true as “Platinum Member”.

Step 4) Define the second logical test to see if Cell B2 is equal to 100.

Step 5) Specify the second value_if_true as “Golden Member”.

Step 6) Define the third logical test to see if Cell B2 is equal to 50.

Step 7) Supply the third value_if_true as “Silver Member”.

Step 8) Hit Enter to see the results.

Pretty nice. Mr. A has 100 points to his credit, so he is rightly designated as a Golden member 🥇

Step 9) Drag the results down the whole list to see the status of the other members too.

The IFS function evaluates the points of all members and returns their status as a member in a single go. Isn’t that spectacular?

Had we done the same by nesting multiple IF functions, the final function would have looked something like below 👇

Multiple nested IF statements and complex formulas. The IFS function sorts it all out.

Example 2

Time to see another and a more detailed example of the IFS function.

So here we have the scores of some students in their annual assessment, and it’s time we grade them based on their scores 🔢

Along with these scores, we have a remarks key that tells what remark is to be assigned to each student based on his score.

Let’s begin writing then.

Step 1) Begin writing the IFS function as follows:

Step 2) As the first logical test, define the first slab of the remark key i.e., see if the score in Cell B2 is equal to or greater than 90.

Step 3) Based on the first logical test and the remarks key, define the first value_if_true from the remarks key table.

Step 4) Define the second logical test to see if Cell B2 is equal to or greater than 75.

Step 5) Specify the second value_if_true as Cell F5 from the remarks key.

Step 6) Define the third logical test to see if Cell B2 is equal to or greater than 60 and accordingly define the value_if_true for it.

Step 7) Then comes the fourth logical test to see if Cell B2 is equal to or greater than 50 and accordingly define the value_if_true for it.

Hold tight! We are almost there 🚴‍♀️

Pro Tip!

If we had written the same formula using nested IF statements, it would have looked like this:

A normal IF function allows users to write a logical test, a value_if_true for it, and then a Value_if_false for it. In a nested IF function, each new IF function is nested in the place of the value_if_false of the preceding function.

However, the IFS function doesn’t allow inputting a value_if_false.

All other students who scored below 50 stands “Failed”. We can specify by writing another logical test but, I will show you how to do it by manipulating it as value_if_false.

Step 8) As the next logical test, write “TRUE” or 1 and then write the value_if_false.

Pro Tip!

Each logical test of Excel yields TRUE or FALSE, where TRUE equals 1 and FALSE equals 0.

The IFS function performs the logical tests in a sequential order i.e., it only performs logical test 2 when logical test 1 has failed.

So, if all the first 4 logical tests fail, Excel will then perform the fifth logical test to see if it turns TRUE(1) or FALSE(0). As the fifth logical_test, we have already specified “TRUE” or 1.

This means this test is true, and Excel will return the value_if_true for this logical test.

In another sense, this works as value_if_false for us. Excel will return this value only if all the previous logical tests fail.

With this, our function stands completed 🎯

Step 9) Hit Enter to see the results.

Before you drag the formula down the cells, make sure to convert the cell references for the remarks to absolute references to make sure these are not changed when the formula is dragged down.

Step 10) Drag the results down the whole list to see the remarks for all the students.

Woohoo! The entire list of scores populated like magic.

Example 3

In another example, we have some customers and the dates when their orders were shipped to them.

However, they have yet not made the payment against their orders. We now quickly want to run an exercise to see which customers are to be subjected to how much late payment surcharge.

If the payment of any customer is delayed by:

• More than 50 days = 10% surcharge
• More than 30 days: 5% surcharge
• More than 10 days: No surcharge

Let’s write the IFS function to automate the surcharge determination.

Step 1) Write the first logical test of the IFS function to see if the days lapsed since the order was shipped to the customer until today’s date exceed 50.

The TODAY’s function yields today’s date and deducting the order shipment date from it calculates the days lapsed in between.

Step 2) Supply the value_if_true for the first logical test as “10%”.

Step 3) Based on the same logic, write the second logical test to see if until today more than 30 days have lapsed since the order was shipped to the customer.

Step 4) Accordingly, hardcode the value_if_true for this logical test as “5%”.

Step 5) Then write the third and final logical test to see if until today more than 10 days have lapsed since the order was shipped to the customer.

Step 6) For this logical test, write the value_if_true as “No Surcharge”.

Step 7) Press enter to see the results.

The IFS function calculates the days lapsed for each order’s shipment and based on the criteria supplied to it, determines that customer A is subject to a 10% late payment surcharge.

Step 8) Quickly drag the formula down the list to calculate the surcharge for all the customers.

There you go! We have the late payment surcharge calculated for all the customers in a jiffy.

This example explains that even within the logical test, you can write formulas. And you can even combine functions within the logical test argument. Similarly, you can hardcode the value_if_true as a text within inverted commas or refer to other cells ⏭

Pro Tip!

Be careful while you use this formula as it uses the TODAY function. TODAY function is a volatile function that’ll update every time to return the date of the present day.

At one part, this might be what you desire if you want an updated surcharge sheet every day – Excel will do it for you.

But if this is not the case and you only want static results that do not update automatically, you can replace them with a hardcoded date.

Conclusion

We have seen multiple examples of using the IFS function in Excel – you must be feeling a pro at it by now. It allows users to input a series of conditions in the same function.

Be careful – sometimes the IFS function can get long and maybe, erroneous too. If you’re facing problems with the IFS function, don’t worry and read the following Excel tutorials that explain how to deal with these errors.

• Do you have problems with IFS? Here’s how to fix any function issues
• Does the IFS function return the #NAME? error? Here’s how to get it to work
• How to Use MAXIFS and MINIFS in Excel: Step-by-Step (2024)