Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

IFS Function

Returns a value that meets the first TRUE condition

What is the IFS Function?

The IFS Function in Excel is a Logical function that was introduced in Excel 2016. The function is an alternative to the Nested IF function and is much easier to use. The IFS function checks if one or more than one conditions are observed or not and accordingly returns a value that meets the first TRUE condition.

 

Formula

= IFS(logical_test1, Value1 [logical_test2, Value2] …, [logical_test127, Value127])

 

Where:

  1. Logical_test1 – First logical test. It is a required argument and is the condition that is used by Excel to evaluate whether it is TRUE or FALSE.
  2. Value1 – Result when logical_test1 is true. If required, we can keep it empty.

The rest of the logical_test and Value arguments are optional; the function allows the user to put 127 logical_test arguments using the IFS function.

 

How to use the IFS Function in Excel?

It is a built-in function which can be used as a worksheet function in Excel. Let’s take an example:

Let’s assume we wish to assign grades to marks earned by students. We can use the IFS Function in the following manner:

 

IFS Function

 

The formula used is:

IFS(A2>80,”A”,A2>70,”B”,A2>60,”C”,A2>50,”D”,A2>40,”E”,A2>30,”F”), which says that if cell A2 is greater than 80 then return an “A” and so on.

Using this formula, the result would be:

 

IFS Function - Example

 

Examples of the IFS Function in Excel

To understand the uses of this function, let’s consider few examples:

 

Example 1 – Using IFS with ELSE

Let’s assume we have a list of items and we need to classify them under three common headings: Vegetable, Fruit, Green Vegetable and Beverage. When we use the IFS function and give the formula =IFS(A2=”Apple”,”Fruit”,A2=”Banana”,”Fruit”,A2=”Spinach”,”Green Vegetable”,A2=”coffee”,”Beverage”,A2=”cabbage”,”Green Vegetable”,A2=”capsicum”,”Vegetable”)

 

IFS Function - Example 1

 

Then, we will get the following result:

 

IFS Function - Example 1a

 

In this example, we have entered multiple logical tests in the IFS function. When a logical test evaluates to TRUE, the corresponding value will be returned. However, if none of the logical tests evaluate to TRUE, then IFS function would give #N/A error. This is what we got for Pepper in B8.

Using ELSE function, we can ensure that this #N/A error is removed. For this we would place a final logical test at the end of the formula that is TRUE and then place a value that shall be returned.

 

IFS Function - Example 1b

 

Here, we added TRUE, “Misc”, this will ensure that Excel returns the value “Misc” in the scenario where none of the previous logical tests in the IFS function evaluates to TRUE.

Now the results would as be as shown below:

 

IFS Function - Example 1c

 

Example 2 – IFS vs nested IF function

Prior to the introduction of the IFS function, we used to use nested IF function. Let’s see how the IFS function is superior to nested IF. Let’s assume we wish to give discounts to customers based on their total purchases from us. So, a customer would get 5% discount for purchases more than $100 but less than $500, 10% discount if he made purchases of more than $500 but less than $750, 20% if he made purchases of more than $750 but less than $1,000 and 30% for all purchases above $1,000.

Let’s see the formula when we would have used nested IF function:

 

IFS Function - Example 2

 

Now, let’s see how it becomes easy to write the formula for same results using IFS:

 

IFS Function - Example 2a

 

Hence, IFS is easier as it allows using a single function to input a series of logical tests. It becomes cumbersome in the nested IF function, especially when we use a large number of logical tests.

 

A few pointers on the IFS function

  1.  #N/A Error occurs when no TRUE conditions are found by IFS function.
  2. #VALUE! Error – We will get this error when the logical_test argument resolves to a value other than TRUE or FALSE.
  3. “You’ve entered too few arguments for this function” error message – This message appears when we provide a logical_test argument without a corresponding value.

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!