Why Use the IFS Function?

The IFS function in Excel makes it easier to handle many conditions at once. Before we had IFS, if you wanted to check different things, you’d use lots of IF statements inside each other aka “Nested IF statements”. This got tricky and messy fast. With IFS, you can check many conditions in just one go, and it gives you the answer for the first true condition.

How to Use the 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.

It looks for the first condition that is true and gives you the matching result. 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).

Note: The IFS function requires Office 2019 (or higher) or Office 365.

Simple Example to Visualize the IFS function

Imagine we have three light switches. Each switch, when turned on, illuminates a light of a specific color. You want IFS to go through the line of switches to find the one that’s on. As soon as it finds a switch that’s on (test), it should report what color the associated light is (value).

To show this process with the IFS function in Excel, you’d write it like this:

=IFS(test1, "blue", test2, "red", test3, "green")

Here’s a breakdown of the function:

  • First, it looks at switch 1 (test1). If switch 1 is on, you get “blue”. If not, IFS moves on to test2.
  • Next, it checks switch 2 (test2). If switch 2 is on, it gives you “red”. If not, IFS moves on to test3.
  • Then, it looks at switch 3 (test3). If switch 3 is on, you get “green”.

If all switches are off, Excel shows an error (#N/A) because it didn’t find any switch that’s on.

❗The order of checking the switches is important because the IFS function stops as soon as it finds the first one that’s on. So, even if the third switch is on, IFS won’t show “green” if it already found an earlier switch on. It stops after finding the first true condition.

Example IFS – Compare Salaries, higher-lower-same

We have a data set where we want to determine if an employee’s salary is higher, lower, or the same as our company’s average salary 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“.

In simple terms, this IFS formula helps you quickly see if someone’s salary is higher, lower, or exactly the same as the average salary. The trick with using TRUE as the last condition is a smart way to catch any cases where the salary matches the average exactly.

Example IFS – 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.

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.

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, "")

How to Insert Emojis in Excel

While typing in the formula bar, press Win + period to open the emoji library.

Emoji picker

Remember, emojis are treated like text and must be enclosed in quotation marks.

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 vs Multiple IF Statements (Nested IF)

Let’s break down the comparison between using IFS and multiple nested IF statements in Excel, focusing on a review system scenario with a 1 to 5 rating system, where 1 is the worst and 5 is the best.

Using Nested IF Statements

To convert numerical ratings into text descriptions, you might start with a nested IF formula like this:

=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.

Simplifying with the IFS Function

Now, compare that to the IFS function approach for the same task:

=IFS(A1=5, "Excellent", A1=4, "Good", A1=3, "Neutral", A1=2, "Fair", A1=1, "Poor", TRUE, "N/A")

At first glance, this formula might not seem much simpler. However, upon closer inspection, you’ll notice it only uses a single IFS function and one set of parentheses, streamlining the process significantly.

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: Multiple Parenthesis Colors in Excel Formulas

When you’re working with nested formulas, Excel will color-pair the parentheses to help you associate the open/close pairs.  Because there are only 8 color pairs, Excel will repeat the colors for the 9th and beyond levels.

The PRO TIP is that the BLACK pair is the only color pair not to be repeated.  This means, when you are closing things off at the end, you can just add close parentheses until you see the BLACK parentheses.  That’s where you need to stop.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.