An IF statement is a function in Excel that enables us to determine whether a particular condition is met.
For every condition, there are only two possible answers:
- TRUE, if the condition is met
- FALSE, if the condition is not met
It uses the following syntax: =IF(logical_test, [value_if_true], [value_if_false])
- logical_test
- Refers to any value or expression that results in either TRUE or FALSE.
- We usually use logical operators in this argument to compare values (see table below for reference).
| LOGICAL OPERATOR | MEANING |
| = | Equal to |
| <> | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
- value_if_true
- Refers to the value returned if the logical_test is true.
- If omitted, the word TRUE becomes the returning value.
- value_if_false
- Refers to the value returned if the logical_test is false.
- If omitted, the word FALSE becomes the returning value.
When working with an IF statement, we can think of it as a flowchart with two pathways – one is for TRUE, and the other is for FALSE.
There are instances, however, when we need to evaluate more than one condition before displaying a value. It’s when the multiple IF statements come in.
We can add the additional IF statement in either the value_if_true or the value_if_false argument. We can even add it on both — turning the flowchart to look something like this:
As you can see, the flowchart has significantly increased in size with just a few IF statements inserted.
It gets even more overwhelming when you look at multiple IF statements from the formula bar in Excel (see example below).
Excel allows up to seven IF functions nested or joined together, but that doesn’t mean you should use all of them.
Multiple IF statements, if improperly curated, can be a source of frustration when fixing issues related to them. It takes a lot of thought to build them and even more to update and debug them.
As much as possible, make it a rule to simplify your formula and take advantage of other available Excel functions to perform the logical comparisons. You could, for example, use VLOOKUP instead of nested IF statements. This way, you won’t have a very lengthy formula to maintain.
Don’t worry. I’ll provide some suggestions below to help you write a shorter formula that still fulfills the requirement of conducting multiple logical tests before displaying a result.
Table of Contents
How to write a multiple IF statement in a cell in Excel
Before writing a multiple IF statement in a cell, create a table containing the conditions and the corresponding results that you want to add to the IF statement.
This table can help you arrange the conditions accordingly. When writing multiple IF statements, remember that the order of the conditions (or the logical tests) matters significantly.
Once the initial logical test is met, Excel no longer goes through the remaining conditions or tests.
In the screenshot below, notice that I have two tables added.
The table on the left shows a list of students with their grade percentages. In column C, I intend to add a formula that gets the corresponding Grade Letter based on the Percentage.
The table on the right shows a list of all the Grade Letters with the corresponding Percentage. These are the set of conditions that I will use for the IF statements.
Now, I’ll start adding the first condition: =IF(B2>=90, “A”
This line says that if cell B2 is greater than or equal to 90, the grade letter is A.
Next, I’ll add the second condition (another IF statement) in the value_if_false argument. The formula will now look something like this:
Keep adding the conditions (the IF statements) in the value_if_false argument until you have added the last one.
The last step is to specify the value to display if all the conditions are unmet. In my case, I added a blank value (“”).
After adding all the conditions, add all the closing parentheses for each IF statement.
That’s it! You have your multiple IF statements all set. If set up correctly, you should be able to generate the correct output.
IFS function: An alternative function
Working on a formula with multiple IF statements can be a handful. You will see lots of “IFs” and parentheses (). Updating it can be confusing and overwhelming at times.
Excel offers an alternative to this, that is, the IFS() function.
IMPORTANT NOTE:
The IFS() function only works on Office 2019 and Office 365.
Unfortunately, this feature is not available on other versions.
The IFS() statement has a pretty straightforward syntax:
=IFS(logical_test1, value_if_true1, [logical_test2], [value_if_true2], [logical_test3], [value_if_true3], …)
It starts with a condition followed by the value to display if the expression returns true.
If you want to add another condition, add a comma and proceed with adding the expression and the corresponding result. And so forth.
Pretty simple, right?
The image above shows the difference between a multiple IF Statement and an IFS statement.
Note that the IFS statement is much shorter. The word “IF” and the parentheses no longer appear more than once, making it easier to read and update.
You must remember that just like the multiple IF statement, the order of the conditions matters in an IFS statement. Once the first condition is met, Excel no longer evaluates the remaining conditions.
Excel allows up to 127 conditions in the IFS statement. But, as mentioned, I don’t recommend you use all of it. Make it a point to simplify your formulas. That will save you from unnecessary headaches in the long run.
VLOOKUP function: Another alternative function
A terrific substitute for the nested IFS is the VLOOKUP function. If your conditions are also based on matching the numbers within a particular range, VLOOKUP is for you.
The VLOOKUP function enables you to find an item from a table. What’s cool about this function is that you have the option to configure how the lookup is performed. It could be by “exact match” or “approximate match”.
In the “Search for Grade Letter” example, it would be more appropriate to use the approximate match method. This option tells Excel to find the closest value in the table.
For it to work, the first column in the reference table (the one that will be used for matching) should be sorted in ascending order (with the smallest number on top).
To do this, let’s start by converting the reference table to something similar to the image below:
1. Get the lowest percentage value and place that on the first column of the table. In your case, this would be the lowest number in the range.
2. In the next column, add the Grade Letter (or in your case, the result that you want to appear if it matches the first column).
3. After that, sort the first column so that it’s in ascending order.
That’s it! Your reference table is now all set. The final step is to add the VLOOKUP formula.
The VLOOKUP function follows this syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value
- Refers to the value to search for in the first column of the reference table.
- It can either be a value or a range.
- table_array
- Refers to the range containing the reference table.
- col_index_num
- Refers to the column number in the table_array that contains the value to be returned if a match is found.
- Note that the first column of values in the table is column 1.
- [range_lookup]
- Set to TRUE to find the closest match in the first column.
- Set to FALSE to find the exact match in the first column.
In the screenshot below, see how significantly short the formula has become compared to when I used the multiple IF statement.
What’s also great about VLOOKUP is that it eliminates the need for you to update the formula to add more conditions or edit the existing ones.
You only need to update the reference table, and you’re all set! Any changes in the conditions and resulting values will immediately take effect.
Conclusion
Multiple IF statements in a cell are OK if you are working with a limited number of conditions. As mentioned, it can be quite taxing to build and maintain. So, give the others (or your future self) a favor and simplify the formula as much as possible. You may also want to explore other Excel formulas that you could use (e.g., IFS, VLOOKUP).