Excel IF Statements, Part II: Nesting Functions

Then, grab cell E2 by its fill handle and drag it down to cell E8 to “relatively” copy it to the rest of the cells in column E.

You see this formula uses a nested AND function for its “logical test”. The AND function will decide if both conditions are true. Remember, the IF function’s second and third arguments return a “value if true” and a “value if false”, so if the AND function returns a “true”, a 2 will be placed in the cell and if the AND function returns a “false”, a 1 will be placed in the cell. Excel will look into the deepest nested function first, then work it’s way out, so the first thing it does is determine the result of the AND function, then it goes out to the IF function and performs that, based on the results of the AND function.

This formula, if entered correctly, should show job level code 2 for Joe and Mary only, because they are the only two who have worked for the company more than five years AND had annual sales greater than $10,000.

Similarly, you can use a nested OR function if you want a job level code of 2 to be applied if an employee meets EITHER criterion, instead of both criteria. A nested OR function works the same way as an AND function, except it will return the 2, if ANY of the criteria are met, instead of requiring that ALL criteria are met.

Put this one in E2 now and copy it down to E8:

In this case, everyone is assigned a job level 2 except John, because he is the only one who did not meet either criterion, since he has not worked for the company for more than 5 years OR had annual sales greater than $10,000.

You can use more than two criteria in nested ANDs and ORs. Simply separate them by commas.

These examples work great when there are only two job levels, but what if there are more than two? In this case, you will have more than two criteria and more than one value if true and value if false.

Let’s say we want to assign a job level 3 if the employee meets BOTH criteria above, a job level 2 if the employee meets EITHER criteria above, and a job level 1 if the employee doesn’t meet any of the criteria above.

Put this formula in E2 and copy it down to E8
 

Notice what we are doing here is basically nesting one IF statement within another. However, though we have two different logical tests and two different values if true, we only have one value if false. You can nest up to 7 IFs in one formula, so that means you can return up to 8 different results (7 different values if true and one value if false).

You can also nest multiple IFs if you want different results based on different values in the same cell. For this example, let’s say all of the employees in the East district will be assigned a job level 4, the West ones will be job level 3, the North ones will be job level 2, and the ones in the South will be job level 1. Our formula would look like this:
 

Note that the value if false is “”, which tells Excel to leave the cell empty if no match is found in column B for that row. If you delete the contents of any cell in column B or change it to anything other than East, West, North, or South, you will see the corresponding cell in column E would now be empty. Remember, you must have something in your third argument (value if false) or Excel will simply enter the text “FALSE” into that cell if it does not find a match.

I think I’ve given you enough information now, so you can build pretty extravagant IF statements. Just remember that limit of seven nested IFs though, because there is no way to increase this using an IF statement. However, when you really need more than eight conditions, there are other functions that will serve you better than Nested IFs. One set of functions are the LOOKUP functions and I will cover them in a later article.

Are we having fun yet? I am, and I hope you are too.

Linda