Summary
The Excel IFS function is a versatile tool that runs multiple tests and returns a value corresponding to the first TRUE result. This function allows you to evaluate multiple conditions without resorting to multiple nested IF statements, leading to shorter and more readable formulas. It’s especially useful when you have several distinct conditions to check sequentially and wish to return specific results for each.
Syntax
=IFS(test1, value1, [test2, value2], ...)
- test1: The first condition to evaluate.
- value1: The result to return if test1 is TRUE.
- test2, value2: [Optional] Additional condition/result pairs.
Return value
- TRUE: If all conditions are TRUE.
- FALSE: If any condition is FALSE.
How to use
Use the IFS function to evaluate a series of conditions, each paired with a corresponding result. IFS will evaluate each condition in the order you provide and return the result for the first condition that evaluates to TRUE. If none of the provided conditions are met, IFS will return a #N/A error. To avoid this and provide a default value, include TRUE as the final test with the desired default value.
Examples
IFS for Ranges
Customer Satisfaction Levels: This formula is used to categorize customer feedback based on satisfaction scores:
=IFS(A1>=8, "Very Satisfied", A1>=5, "Satisfied", A1>=3, "Neutral", TRUE, "Needs Improvement")
It assesses the satisfaction score in A1 and assigns a category based on where the score falls within specific ranges, defaulting to “Needs Improvement” if it doesn’t meet any of the conditions.
IFS for Discrete Values
Product Category Assignment: This formula assigns product categories based on specific product codes:
=IFS(B1="P100", "Electronics", B1="P200", "Home Appliances", B1="P300", "Books", TRUE, "Uncategorized")
It checks the product code in B1 and assigns the corresponding category. If the code doesn’t match any predefined ones, the product is labeled as “Uncategorized.”
IFS with Default Value
Employee Department Sorting: This formula sorts employees into departments based on their employee ID and provides a default value for new or unrecognized IDs:
=IFS(LEFT(C1,2)="HR", "Human Resources", LEFT(C1,2)="IT", "IT Department", LEFT(C1,2)="MK", "Marketing", TRUE, "Unassigned")
It evaluates the first two characters of the employee ID in C1 to determine the department and categorizes the employee as “Unassigned” if the ID doesn’t match any known department prefixes.
Additional Notes
- The order in which you list the conditions is crucial. Arrange them from most specific to most general or in a logical order that fits your scenario.
- IFS does not inherently include a default value for scenarios where all conditions are FALSE. To handle this, add TRUE as the final condition with your preferred default value.
- Ensure all logical tests return TRUE or FALSE. Any other result will cause IFS to return a #VALUE! error.
- The IFS function is available in Excel 365 and Excel 2019. For earlier versions, consider using nested IF statements or the CHOOSE function as alternatives.