Excel nested IF functions tutorial | Download free spreadsheet

This is an example of how use multiple nested IF functions in Excel. In this quick tutorial we will look for a certain value from multiple cells and print out either number 1 or number 2. The example can be seen in the image below.

Nested if functions in Excel tutorial



The idea is to check if the value in column E ("value to look for") can be found in any of the columns 1 through 4 and just print out a number depending on the outcome. If the value is found, we will print out number 1, if it is not found, we will print out number 2.

The syntax


Here is the syntax to put in the cell F5:
=IF(E5=A5;1;IF(E5=B5;1;IF(E5=C5;1;IF(E5=D5;1;2))))

Note, depending on your regional settings, you might have to use comma (,) instead of semicolon (;) as a separator:
=IF(E5=A5,1,IF(E5=B5,1,IF(E5=C5,1,IF(E5=D5,1,2))))

Explanation

1. When we nest multiple IF functions, we have to place each new function to the VALUE IF FALSE part of the previous function. For simplicity's sake i'll explain this with two nested functions. Example with two IF functions below. As you see, the second function is placed in the "value if false" part of the previous function as marked with red:
=IF(E5=A5;1;IF(E5=B5;1;2))
2. If none of the logical tests equal to TRUE, the VALUE IF FALSE of the whole formula comes to the "value if false" part of the last function. In this case we will print out number 2 if neither of the logical tests equal to true (marked with red):
=IF(E5=A5;1; IF(E5=B5;1; 2 ))
3. All the nested IF functions are closed in the end of the whole formula. Marked with red:
 =IF(E5=A5;1;IF(E5=B5;1;2 )) 
Noteworthy: when a logical test equals to true, Excel stops testing and prints out the VALUE IF TRUE of that particular function. This means you have to be careful of the order of your nested functions. Check out my IF function tutorial for more information on IF function.

Free Download nested Excel IF function example spreadsheet

 

No comments:

Post a Comment