Download the practice workbook 👉 HERE and follow along.
Why Use the IFS Function?
Before the IFS function, handling multiple conditions in Excel meant nesting several IF statements, which often became messy.
The IFS function allows you to evaluate conditions one by one and return the result for the first true condition. This not only makes your formulas easier to read but also reduces errors.
IFS Function Syntax
The IFS function checks conditions one by one in the order you list them. The function can handle up to 127 of these conditions.
Here’s how you write it:
=IFS(test1, value1, [test2, value2], …)- test1: This is the first thing it checks.
- value1: This is what you get if test1 is true.
- test2, value2: Second test and results you can add. (optional)
It checks each thing one by one until it finds something true. Then, it stops and gives you the result for that true thing. If it finds nothing true, it shows an error (#N/A).
💡 The IFS function requires Office 2019 (or higher) or Microsoft 365.
Simple Example to Visualize the IFS function
Imagine you have three light switches (test1, test2, test3), each controlling a different color light: blue, red, and green.
The goal is for Excel to identify which light is on using the IFS function.
Here’s how it works:
=IFS(test1, "blue", test2, "red", test3, "green")- Step 1: Check switch 1. If it’s on, Excel returns “blue”.
- Step 2: If switch 1 is off, check switch 2. If it’s on, Excel returns “red”.
- Step 3: If switch 2 is off, check switch 3. If it’s on, Excel returns “green”.
If none of the switches are on, Excel will return an error (#N/A) because it didn’t find any true condition.
❗The order matters! The IFS function stops as soon as it finds the first switch that’s on. Even if later switches are on, they won’t be checked if an earlier switch was already on.
Example: Comparing Salaries
Imagine you need to compare employee salaries against a company average of $50,000:
The formula for the first employee’s salary test will be placed in cell C6.
=IFS(B6>$C$3, "Higher", B6<$C$3, "Lower", TRUE, "Same")The IFS Formula Explained:
- First, the formula checks if the salary in cell B6 is higher than the average salary in cell C3. If yes, it shows “Higher“.
- Next, it checks if the salary in B6 is lower than the average in C3. If this is true, it shows “Lower“.
- Finally, the part that says TRUE, “Same” acts as a catch-all for any salary that is exactly the average. It means if the salary didn’t fit in the first two categories, it must be equal to the average, so it shows “Same“.
This formula checks if an employee’s salary (B2) is higher, lower, or the same as the average (C1). The “TRUE” condition acts as a catch-all for any cases not covered by the previous conditions.
Example: Show Emoji for Specific Rating
In this example, we want to show a specific emoji based on the participant’s level of satisfaction selected from a dropdown list.
As an extra touch, we want to conditionally format (color) the Emojis.
1. Creating the Dropdown List
- First, we create a dropdown list for participants to choose their level of satisfaction.
- This is done using Excel’s Data Validation tool. For details on setting this up, check out this article.
2. Displaying Corresponding Emojis
- In cell C17, we’ll input the IFS formula that displays a unique emoji based on the selected satisfaction level:
=IFS(B17="Amazing", "😍", B17="Good", "🙂", B17="Average", "😶", B17="Bad", "😪", TRUE, "")3. How to Insert Emojis in Excel
- While typing in the formula bar, press Win + period to open the emoji library.
💡Emojis are treated like text and must be enclosed in quotation marks.
4. Adding Color with Conditional Formatting
To make our feedback visually appealing, we’ll color-code the emojis based on the satisfaction level using Conditional Formatting.
Steps to Apply Conditional Formatting:
- Highlight the cells containing the IFS function (in this example, C17 through C24).
- Go to Home tab, find the Styles group, and click on Conditional Formatting.
- Choose Highlight Cells Rules -> Equal to….
- In the dialog box, press Win + . to bring up the emoji list and select the emoji you want to color-code.
- Choose your color scheme. For more options, click on Custom Format….
- Repeat these steps for each emoji to apply different colors accordingly.
IFS Function vs. Nested IF Statements
Using the IFS function simplifies what could otherwise be a complex series of nested IF statements.
For example, to convert ratings (1-5) into text descriptions:
Nested IF:
=IF(A1=5, "Excellent", IF(A1=4, "Good", IF(A1=3, "Neutral", IF(A1=2, "Fair", IF(A1=1, "Poor", "N/A") ) ) ) )This formula can be tricky to manage because it involves five IF functions nested within each other, requiring you to keep track of five sets of parentheses. It can quickly become a headache, especially as the complexity increases.
IFS Function:
=IFS(A1=5, "Excellent", A1=4, "Good", A1=3, "Neutral", A1=2, "Fair", A1=1, "Poor", TRUE, "N/A")The IFS function is easier to read, modify, and reduces the likelihood of errors.
Key Differences:
- Nested IFs Complexity: With nested IFs, you could end up with up to 64 levels of IFs. Imagine trying to navigate through all those parentheses!
- IFS Simplicity: The IFS function allows for up to 127 decisions within a single, more manageable function.
Pro Tip: How to Easily Match Parentheses in Excel Formulas
When you use nested formulas in Excel, the program color-codes parentheses to help you match them correctly.
Excel uses 8 color pairs, and once you go beyond that, the colors repeat.
Here’s a handy tip: The black parentheses never repeat!
When you’re closing a complex formula, just keep adding closing parentheses until you see the black pair. That’s your signal to stop.
Pro Tips for Using IFS Function
- Order Matters: The IFS function evaluates conditions in the order they appear. Always place the most specific conditions first.
- Avoid #N/A Errors: Include a final condition like TRUE, “N/A” to catch any unmatched scenarios.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel IFS function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
