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

 

2 comments:

  1. I am regular visitor, how are you everybody? This paragraph posted at this web site is really nice. outlook 365 email login

    ReplyDelete
  2. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete