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 IFs, you can have up to 64 levels of IFs 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.
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.
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.
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.
- Shorter formula
- Easier to read and manage
- Potentially unlimited number of choices
- 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.
Feel free to Download the Workbook HERE.
Excel ESSENTIALS for the REAL World (The Complete Excel Course)
From Excel Beginner to Professional
Learn Excel from Scratch
OR Improve Your Excel Skills to Become More Confident
Check out our best-selling course