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.
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 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.
Using IFS to Compare Salaries
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.
We wish to break from the old ways of doing things and use the new IFS function.
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.
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.
