# Excel IFS Function

One of the most used features of Excel is the ability to evaluate a situation and act in different ways depending on the evaluation outcome.

**IF Statements** give us this ability.

If you perform an Internet search for “*Top 10 things every Excel power user needs to know*”, **IF Statements** will be in every list you read.

The problem with IF Statements is that they can easily get out of control. Lengthy, multi-level **IF Statements** will leave even the most ardent Excel guru reaching for an adult beverage.

Microsoft heard our cries for relief and a few years ago delivered to us from on high the **IFS Function**.

Let’s see how this wonderful little function can tame the wild beasts that are nested IFs.

## NOTE: The IFS function requires Office 2019 or Office 365.

Here’s the scenario: We have a review system where questions are answered with a **1 to 5** rating system; **1** being the *worst* and **5** being the *best*.

We want to see words in place of the numbers, so we write a nested IF formula like the following:

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

Does that make your head hurt?

Now let’s see how the **IFS** function handles the same task.

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

I know what you’re thinking, “That doesn’t look much better at all!”

Look closely. The first version has **5** **IF** functions nested within one another. That’s **5** sets of parentheses to wrestle with.

The second version has but a single **IFS** function; **1** set of parentheses.

With nested **IF**s, you can have up to **64** levels of **IF**s within one another. Can you imagine keeping track of all those parentheses?

With the **IFS** function, you can have up to **127** decisions in a single function.

*PRO TIP:*

*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 9^{th} 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.*

# The Syntax of IFS

The **IFS** function has the following syntax:

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

** logical_test1** – is a condition that evaluates to TRUE or FALSE

** value_if_true1** – is the result to be returned if logical_tes1 evaluates to TRUE. This can be empty.

**[logical_test2…logical_test127]** – are conditions that evaluates to TRUE or FALSE.

**[value_if_true2…value_if_true127]** – Results to be returned if corresponding logical test is TRUE.

# Testing the Salaries

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 first 2 test are probably obvious; if the person’s salary if greater than the average, display the text “Higher”. If the person’s salary is less than the average, display the text “Lower”.

The puzzling part of the formula is the ‘ TRUE, “Same” ’

Since the **IFS** only cares about *TRUEs* and *FALSEs*, we can force-feed the last test a **TRUE** thereby acting as a catch-all for any values that are equal to the average salary.

In other words, “*If you make it this far, you must be equal to the average salary.*”

# 😊 EMOJI IFS ☹

Like the earlier example with a number ranking survey, we want to inquire as to the satisfaction with a team-building event.

Instead of numbers, we will give the participant a dropdown list of choices.

This dropdown list was generated using the Data Validation tool. For more information on how to construct and manage this feature, click here.

The objective is to display an EMOJI that corresponds to the selected level of satisfaction.

As an extra touch, we want to conditionally format (color) the EMOJIs.

In cell **C17**, we construct the following formula:

How did you make those cool EMOJIs?

While writing the formula in the **Formula Bar**, press the Windows – period key combination.

This will give you access to the EMOJI library for use in formulas.

Keep in mind, EMOJIs are no different than text and must be enclosed in quotation marks.

# Giving Our EMOJIs Color

We can use **Conditional Formatting** to add a bit of color to our EMOJIs.

We can apply colors to the EMOJIs based on the EMOJI generated from the **IFS** function.

- Select the cells that contain IFS functions. In this example, cells
**C17**thru**C24**. - Select
**Home (tab) -> Styles (group) -> Conditional Formatting -> Highlight Cells Rules -> Equal to…** - In the
**Equal To**dialog box, press the**WIN-period**key combination to bring up the EMOJIs list. - Pick the EMOJI you are applying color towards.
- Set the desired color scheme (
*hint: select*)**Custom Format…**to get access to all the colors and styles. - Repeat steps
**1**thru**5**to apply different colors to other EMOJI options.

# Alternative to Lengthy IFS

Everyone has a limit as to how many levels in a nested IF or options in an IFS they can tolerate. I can’t say I’d want to experience a 127 option IFS function.

When you reach your limit, you may want to switch to a lookup function.

Consider the following table and associated function.

=IFNA(VLOOKUP(B28, $H$28:$I$31, 2, FALSE), "")

You can see how this applies to the following data set.

## PROS:

- Shorter formula
- Easier to read and manage
- Potentially unlimited number of choices

## CONS:

- Requires a separate list
- Must be protected from user changes and mischief

# BONUS – USING the New XLOOKUP Function

If you are running **Office 365**, you can also take advantage of the new **XLOOKUP** function.

Using the same data, the formula would read as follows.

=XLOOKUP(B28, $H$28:$H$31, $I$28:$I$31, "")

For an in-depth explanation of the **XLOOKUP** function (*and you really should learn this one*) click here.

## Practice Workbook

Feel free to Download the Workbook HERE.