Excel IF Condition - : nested IF formulas with multiple conditions

 We started to learn the nuts and bolts of the Excel IF function. Below few IF formulas for numbers, dates and text values as well as how to use the IF function with blank and non-blank cells.

However, for powerful data analysis, you may often need to evaluate multiple conditions at a time, meaning you have to construct more sophisticated logical tests using multiple IF functions in one formula. The formula examples that follow below will show you how to do this correctly. You will also learn how to use Excel IF in array formulas and learn the basics of the IFEFFOR and IFNA functions.

Example 1 : How to use Excel if Condition.

In this example we are going to set target for each employees based on their age group randomly.

Target Based on the employee age group.

Age Group	Target
20 to 25	20
25 to 30	25
31 to 35	30

  

Set Target for below employees Based on their age. 

Names	Age	Target
SWAMY	28
GANESH	29
NIRMALA	34
PRASANNA	30
H SHIVANNA	23
PUNEETH	35
PRAVEEN	23
MAHESH	21
MAHINDRA	24
VASANTHA	25

Using IF AND function. condition we can complete this task easy,  If your logical test contains the AND function, Microsoft Excel returns TRUE if all the conditions are met; otherwise it returns FALSE.

To illustrate the point better, let's have a look at a few IF examples with multiple conditions.

Step 1. =IF(AND(B2>=20,B2<=25),F3,"Na")

The formula tells Excel to return appropriate target that matching age group  if a value in cell B2 >=20 AND a value in cell b2 <=25. Otherwise, the formula returns "Na". The screenshot below proves that our Excel IF /AND function is correct:

As above Example Employee name SWAMY age 28 does not match in Age group 20 to 25 so its still showing Na As we defined like.

Step 2. =IF(AND(B2>=20,B2<=25),F3,IF(AND(B2>25,B2<=30),F4,"Na"))

The formula tells Excel to return appropriate target that matching age group  if a value in cell B2 >=25 AND a value in cell b2 <=30. Otherwise, the formula returns "Na". The screenshot below proves that our Excel IF /AND function is correct:

Step 3. 

Add the same condition As step 1 and step 2 for age group 31 to 35 as below example.

=IF(AND(B2>=20,B2<=25),$F$3,IF(AND(B2>25,B2<=30),$F$4,IF(AND(B2>25,B2<=30),$F$5,"NA")))

Drag the formulas to rest of all cells to respective employees and see the Magic rest of all automatically filled see the below image, its really  easy isn't it..??

Try by your self, if you have any doubts comment below...

Do you like this page share on Google+ or other social networks...