IFS Function in Excel

The IFS function in Excel is a really handy tool that makes it easier for you to check different conditions and get specific results based on those checks. It's like having a simpler way to ask multiple "if this, then that" questions within your spreadsheet. Introduced in Excel 2016, this function allows you to set up a series of conditions (like "if a number is greater than 10") and corresponding results (like "then say 'Big'"). What's great about the IFS function is that it checks each condition in the order you give it, and as soon as it finds one that's true, it gives you the result for that condition. This makes your Excel work simpler and your formulas much cleaner, especially when you have a lot of different conditions to check.

IFS Function: Supported versions

The IFS function in Excel was introduced in Excel 2016. Therefore, it is supported in the following versions:

Excel 2016 and later: The IFS function is available as part of Excel 2016 and all subsequent versions, including Office 365 and Excel 2019.
Excel for Microsoft 365: Users with the Microsoft 365 subscription have access to the latest version of Excel, which includes the IFS function.
Excel for Mac (2016 and later): The IFS function is also available in Excel for Mac from the 2016 version onwards.
Excel Online: The online version of Excel, accessible through a web browser as part of Microsoft 365, supports the IFS function.

It's important to note that if you are using an older version of Excel (such as Excel 2013 or earlier), the IFS function will not be available. In such cases, users typically rely on the nested IF function to achieve similar functionality, although this can be more cumbersome and less intuitive than using the IFS function.

Syntax for IFS Function

The IFS function in Excel is used to test multiple conditions and return a value corresponding to the first TRUE condition. The syntax for the IFS function is as follows:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

Arguments

logical_test1	This is the condition you want to test.
value_if_true1	The return value if logical_test1 result is TRUE. Can be left blank.
logical_test2…logical_test127	These are the optional conditions you want to test. Each condition is a logical test that can be either TRUE or FALSE.
value_if_true2…value_if_true127	These are the optional values that correspond to each condition. The value corresponding to the first TRUE condition is the one that the function returns.

Example

Our Excel's IFS function example will demonstrate how the IFS function can efficiently process chest measurements and assign them to standard size groups such as Small, Medium, Large, and so on. By inputting the chest measurement into Excel, the IFS function will automatically evaluate and categorize it based on the size ranges you define. This approach not only simplifies the sizing process but also ensures consistency and precision in your size categorization.

=IFS(measure<=38,"S",measure<=41,"M",measure<=45,"L",measure>45,"Not found!")

the formula goes through each pair of test and value until it finds one where the test is TRUE. In our example, the named range 'measure' has a value of 41, so when the formula gets to the second test, it finds that this test is TRUE. As a result, the function then returns the value "M."

Download Workbook

IF Function vs IFS Function

Most common question about IFS function is "What is the different between IF and IFS function in Excel?". Let's have a look at them to clarify the difference;

The IF and IFS functions in Excel are both used for conditional logic, but there are key differences in how they operate and are applied:

Number of Conditions Tested:
- IF Function: Tests a single condition at a time. It performs a logical test and returns one result for a TRUE outcome and another for a FALSE outcome.
- IFS Function: Designed to test multiple conditions at once. It evaluates each condition in the order provided and returns a result for the first TRUE condition.
Syntax Complexity:
- IF Function: The syntax is IF(logical_test, value_if_true, value_if_false). For multiple conditions, nested IF statements are used, which can make the formula complex and hard to read.
- IFS Function: The syntax is simpler and more straightforward for multiple conditions: IFS(condition1, value1, condition2, value2, ...). It eliminates the need for nesting multiple IF statements.
Ease of Use:
- IF Function: Can become cumbersome and less intuitive when dealing with multiple conditions due to nested formulas.
- IFS Function: More user-friendly for multiple conditions as it avoids deep nesting and makes the formula easier to construct and understand.
Functionality:
- IF Function: Offers more flexibility for single conditions or when different actions are needed for the TRUE and FALSE outcomes.
- IFS Function: Best suited for situations where you have several conditions to check and you need to return a specific value for the first TRUE condition met.
Error Handling:
- IF Function: You can more easily include specific error handling for the FALSE outcome.
- IFS Function: If none of the conditions are met, it returns an error. A catch-all condition is often added at the end of the IFS formula to handle this.

Understanding Conditional Formatting with IFS Function in Excel:

Using the IFS statement in Conditional Formatting in Excel is a bit of a workaround, as Conditional Formatting typically relies on simpler conditions or direct formula evaluations. The IFS function isn't directly used within Conditional Formatting; however, you can employ a similar logic to achieve multi-condition checks.

How to Mimic IFS Logic in Conditional Formatting:

The IFS function in Excel allows for testing multiple conditions and returning a corresponding value as soon as the first condition is met. To emulate this functionality in Conditional Formatting, you need to establish a series of distinct rules.

For instance, if you have an IFS statement like IFS(A1 > 10, "High", A1 > 5, "Medium", TRUE, "Low"), you would create three conditional formatting rules:

One rule where the condition is A1 > 10 to format as "High".
Another where A1 > 5 to format as "Medium".
And a final one for any other case (e.g., A1 <= 5) to format as "Low".

When applying rules in Conditional Formatting to mimic the IFS function in Excel, it's essential to apply them to the same range of cells. The key is to ensure that these rules are ordered correctly because, similar to the IFS function, Excel will only apply the formatting of the first true condition it encounters and stop evaluating the rest. This order can be effectively managed within the Conditional Formatting Rules Manager. For each of these rules, you have the flexibility to specify custom formatting options, such as changing the font color or cell color, to suit your requirements whenever the conditions are met. Additionally, one of the great features of these rules, much like the IFS function, is their ability to dynamically

Tips

You can test up to 127 different conditions.
If you need a default value, enter TRUE for the last test. The function will evaluate the last TRUE test if there are no other successful tests.
Use the SWITCH function if you have a single expression with multiple outcomes.
Clearly Define Conditions: Be precise in defining the conditions for each part of the IFS statement. Ensure that each condition is mutually exclusive to avoid overlap and confusion.
Order Conditions Wisely: Arrange conditions in a logical order, typically starting with the most specific or likely to be true at the top. Excel evaluates conditions in the order they are listed and stops at the first true condition.
Combine with Other Functions: Don't hesitate to combine IFS with other functions for more complex conditions. For example, using AND() or OR() within an IFS condition can help you check multiple criteria at once.
Error Handling: Include a condition for error handling, like using ISERROR() to catch and manage errors within your IFS statement.
Fallback Default Condition: Always include a default condition at the end (usually TRUE, value) as a catch-all for any scenarios not covered by your specified conditions.
By following these tips, you can leverage the IFS function effectively, making your Excel spreadsheets more efficient and user-friendly.

Issues

#N/A ERROR:

The #N/A error in the context of the IFS function in Excel typically occurs when none of the specified conditions in the function are met. The IFS function is designed to evaluate a series of conditions in the order they are presented and return the corresponding value for the first condition that evaluates to TRUE. If all the conditions evaluate to FALSE, and there is no default condition set to catch this scenario, Excel will display the #N/A error.

For example, consider the function: IFS(A1 > 10, "Over 10", A1 > 5, "Over 5"). If the value in cell A1 is 4, neither of the conditions (A1 > 10, A1 > 5) are met. In such a case, Excel doesn’t know what value to return, resulting in a #N/A error.

To avoid the #N/A error, it's a good practice to include a final, catch-all condition in the IFS function, usually TRUE, which acts as a default value if none of the other conditions are met. For instance, modifying the previous example to IFS(A1 > 10, "Over 10", A1 > 5, "Over 5", TRUE, "5 or less") ensures that "5 or less" is returned when A1 is 4, preventing the #N/A error.

#VALUE! ERROR:

The #VALUE! error in the context of the IFS function in Excel typically occurs when there is a problem with the type of data in one of the conditions or values in the function. This error indicates that there is a mismatch in the expected data type or that Excel is unable to interpret or calculate one of the components of the function.

Common reasons for a #VALUE! error in an IFS function might include:

Incompatible Data Types: Using a text string in a place where a number is expected, or vice versa. For example, if one of the conditions is trying to perform a mathematical operation on a text string.
Incorrect References: If the function references a cell or range that contains data not suitable for the operation, like trying to perform a numeric comparison on a cell containing text.
Improper Function Usage: Misusing Excel functions within the IFS conditions. For instance, providing wrong arguments to a nested function which results in an error.
Formula Syntax Errors: Mistakes in the formula construction, such as missing or extra commas, parentheses, or other syntax elements.

To resolve a #VALUE! error in an IFS function, carefully check each condition and corresponding value to ensure that the data types are compatible, and the formula syntax is correct. Additionally, verify that any nested functions or cell references within the IFS function are accurate and appropriate for the intended calculation.

Important Reminders

Mutually Exclusive Conditions: Ensure that the conditions in your IFS function are mutually exclusive to prevent unexpected results. Overlapping conditions can lead to confusion in which condition is actually being met.
Performance on Large Datasets: While IFS is efficient, its performance can be impacted in workbooks with a large number of complex formulas. It's important to balance its use with other Excel optimization techniques.
Limit on Number of Arguments: There is a limit to the number of arguments you can include in the IFS function. Exceeding this limit that we mentioned on the TIPS section can result in errors, so it's important to be concise in your conditions.
Readability and Maintenance: Complex IFS functions with many conditions can become difficult to read and maintain. It’s crucial to keep them as simple as possible and to use comments for documentation.
Not Available in Older Versions: The IFS function is not available in Excel versions prior to 2016. Users of older versions need to rely on nested IF functions or upgrade their Excel version.
Use with Other Functions: IFS can be combined with other Excel functions for more complex scenarios, but this should be done judiciously to maintain clarity and performance.