How to Use Multiple IF Statements in Microsoft Excel

An IF statement in Excel runs a logical test that returns one value if a condition is met and another value if it isn't. Using a single Excel IF statement can only accommodate basic operations, but if you nest multiple IF statements, you can perform complicated tasks. The only downside is that you have to be careful when constructing the formula to avoid making errors. Alternative Excel functions can do the same job with less risk of error. Learn the basics of nested IF statements in Excel and the alternatives to find the best approach for your needs.

The IF function in Excel essentially says "if this is true, return x value, but if it isn't, return y value." For example, if you were checking to see whether various departments in your business were under or over budget, you could compare their expenditures with their budgets using the function and have it return "Over budget" if the department spent too much or "Within budget" if it didn't. Generally speaking, you use comparison operators to form your statement, such as:

• = (equal to)

• (greater than)

• = (greater than or equal to)

• < (smaller than)
• <= (smaller than or equal to)
• <> (not equal to)

Understanding the syntax of the Excel IF statement is a crucial step toward learning to use it properly. The format for the function is IF(logical_test, value_if_true, [value_if_false]) where the square brackets indicate that this last argument is optional. These are easy to interpret: The logical_test means the statement you want to test, value_if_true is the spot for what you want Excel to show if the test is true, and [value_if_false] is the optional spot for what you want Excel to display if the test is false.

This is enough for you to use the Excel formula for if-then statements, which can be demonstrated using the "Over budget/within budget" example from earlier. Imagine you have the amount spent by various departments in column B and the amount budgeted in column C, with the values starting on the second row. In cell D2, you would type =IF(B2>C2, "Over budget", "Within budget") to return the result you're looking for (noting that you need to use quotation marks to get it to return the text inside). The first part says "if the value in B2 is greater than the value in C2" (if the expenditure is bigger than the budget), the second part says "then return the text Over budget and the third part says "if not, then return Within budget."

Making the most of the Excel IF statement tool means being able to combine multiple IF statements when you have a task that's suitable for it. You can include a second IF statement within your original IF statement, and you can include another IF statement within that and so on. This is particularly useful if you want to categorize something into one of several groups using a single formula. For example, you can use nested IF statements to convert the scores students achieved on a test into a grade.

To use multiple IF statements correctly, think about the logic of what you're asking Excel to do. Essentially, after your condition (the first argument in the function), you can add another IF statement into the value_if_true or value_if_false spaces, to tell Excel what to do next. Think about this as getting Excel to check the cell against a second criterion if the cell in question doesn't meet the first criterion (or indeed, if it does meet the first one), and you can continue this process for a long time. In other words, you're turning "if this, then that" into "if this and this, then that" or "if not this but this, then that."

Imagine you've given students a test where scoring 85 percent or higher is an A, between 70 and 85 percent is a B, between 55 and 70 percent is a C, between 40 percent and 55 percent is a D and anything lower is a Fail. The scores are in column B, from row 2 to row 11. You can use a carefully constructed Excel IF function with multiple conditions to categorize the students' scores.

The first part is easy (for a score in B2, in this example): =IF(B2>=85, "A") tells Excel to return an A if the student scored 85 or higher. Nesting enables you to add other grades, making use of the third argument (what to do if the condition is false) to add extra conditions: =IF(B2>=85, "A", IF(B2>=70, "B")), which tells Excel to return an A for scores of 85 or higher, but if not, return a B if the score is over 70. Continue in this way to complete the formula, using the third argument for a new IF statement each time: =IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail"))))

This looks complicated, so breaking it down should cement the idea. The first two arguments say "if the score in B2 is 85 or more, then the student gets an A," and the third says, "otherwise, check the following condition." The second IF leads to this statement, "(for students scoring less than 85) if the score in B2 is 70 or higher, then the student gets a B, and if not check the following condition." The third IF says, "(for students scoring less than 70) if the score in B2 is 55 or higher, the student scored gets a C, and if not, check the following condition" and the fourth says "(for students scoring less than 55) if the score in B2 is higher than 40, the student gets a D, if not, the student fails."

Provided you enter the formula for your IF statement properly, it will return one of the options you've given to Excel. However, if you make a mistake, the formula might return a 0 or #NAME? in the cell containing your formula.

A 0 error means that either the value_if_true or value_if_false argument is empty. Fix this problem by ensuring a value is entered in both arguments, or at least, in the value_if_true argument. The value_if_false argument can be completely removed, and it will return FALSE if you don't put a second comma in after the value_if_true field.

A #NAME? error tells you that you've probably misspelled or mistyped something in the formula. This might be a little harder to fix because it could be any part of the formula, but the best advice is to double-check everything or write out the formula again.

The previous example nested IF function formula got quite long, which is one of the major downsides of using multiple IF statements in Excel. They can become complicated, and it's easy to make a mistake because you have to follow the logic through carefully to make sure everything is working in the way you intend. Microsoft points out that an incorrectly nested formula might even return the right result most of the time, but incorrect results on rare occasions. These are hard to spot.

Not putting the conditions in the right order is a common mistake. In the graded tests example, if you started with the lower score =IF(B2>=40, "D", …" and proceeded to list the others in ascending order, even a score of 100 would return a D. This is because the first condition is checked first, so if you started with "if cell B2 is 40 or higher, return a D" it would class any score of 40 or over as a D without checking anything else because it meets the first condition. This is a perfect example of why you need to follow the logic of your formula through.

You also need to make sure you add the right number of brackets to the end of multiple IF statements to close the formula properly. Excel helps with this by color coordinating and highlighting the brackets as you work.

Technically you can nest up to 64 IF functions in a single formula, but Microsoft doesn't recommend using the function like this for the reasons above. Things get complicated quickly, and chances are, if you're trying to nest more than 10 IF statements, there is probably a better tool for the job included in Excel already.

The most convenient alternative to the IF function is the IFS function, which is a version of the function that performs the same task as nesting in a single function and can handle up to 127 conditions. The IFS function has a simpler syntax: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3]…) and so on. Essentially, this works like nested IF statements, except subsequent IF statements occupy the space after the "value if true" without needing to bring up the function again.

The formula in the test grade example can be written much simpler with the IFS function. Instead of:

=IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail"))))

simply write

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", TRUE, "Fail")

The TRUE in the spot before last says "if none of the other values are met, then return the next value."

This feature is only available in Excel 2019 or Excel for Office 365.

The VLOOKUP function in Excel is often a simpler alternative to multiple IF statements and is available on any version of Excel from 2007 onward. It isn't ideal for all situations because it either returns results for an exact match or for the closest number, so it couldn't be used for grades like the previous example. However, in many situations, it can save you a lot of work writing out a formula, although you have to construct a table with possible values in one column, such as products your store sells, and what you want the function to return, such as the price of the product, in another column, for example.

The syntax for VLOOKUP is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) and learning each of the components shows you how to use it. The lookup_value argument is the value you want to check, which needs to be a cell in the same column as the start of the table you want to check it against, either above or below it. The table_array is the range of cells containing your table of information, such as products and their corresponding prices, and col_index_num is the column you want Excel to look in to find the value to return, with 1 indicating the left-most column, 2 indicating the next one and so on. Finally, [range_lookup] is optional, but enter "TRUE" if you want Excel to base the answer on the closest value or "FALSE" if you only want an exact match.