We have already seen, how to use the **IF** function in basic Excel formulas. Using the **IF** with other functions together, in a complex formula, allows you to test multiple conditions and criteria. In this article, we are going to analyze Excel **IF **function/statements multiple conditions use.

When you will be doing some complex data analysis, you might be needed to analyze more than one condition at a time. It means you might have to make a complex Excel formula that will test multiple conditions.

Today, in this article, weâ€™ll learn three quick and suitable ways to apply the **IF** function Excel multiple conditions effectively with appropriate illustrations.

## Evaluation of Nested IF Functions in Excel

Look at the below example where we stated some results based on some conditions. Here the formula, which is written below the table, is used for cell **D5**. This formulated cell was dragged down to get the result for the whole column. The functions in **D5 **are:

`=IF(C5<=50,"Good",IF(C5<=70,"Average",IF(C5<=80,"Better",IF(C5<=90,"Best"))))`

The above example follows the nested **IF **functions. The nested **IF **functions in Excel follow some rules. As the name if defines a condition so **IF **will look for a certain condition. If a certain condition is achieved then it will give us a value. If the condition is not achieved then it will look for another defined result. In nested **IF** functions, we work with so many **IFs**. While defining results in a certain cell the defined formula for that specific cell will look for the exact condition. If the condition matches then you will get the result.

Things are more complicated while working with numbers. In nested **IF **functions you have to be very careful while defining conditions as the same conditions can be applied to other numbers too. Like number **49 **is less than **50 **and **80**. If you have a condition for both number **50 **and number **80 **then you might get an accurate result. So proper nesting is very important and it has to be maintained from either the lowest number to the highest number or the highest number to the lowest number. Observe the flowchart below for a better understanding of the evaluation.

Follow the following flowchart for the example stated above to get a proper idea of how the nested **IF **functions work.

We are showing the above Excel formula in this flowchart. The formula is easy to read from this flowchart. When you will make complex formulas, you can think in this way.

## How to Use IF Function with Multiple Conditions in Excel: 3 Suitable Ways

Letâ€™s get introduced to our dataset first. I have *the Name of the students, CGPA* and *number of credit earned by the students* on my sheet and Iâ€™ll use **the IF function** in Excel with multiple conditions using this dataset.

### 1. Calculate Grade with Nested IF Function

Let`s say we have a number of students who obtained some specific numbers in some specific subjects. We want to convert these numbers into the equivalent grade. The grades with the number range gives below. For this, we can use **the nested IF function**. The procedure is very simple. Letâ€™s follow the instructions below to learn!

**Steps:**

- Initially, our table looks like the picture below where we want to obtain the equivalent grades.

- Write this formula in cell
**D5**and drag this formulated cell to the rest of Column**D**. By doing this you will get the result for the whole column.

`=IF(C5<50,"F",IF(C5<60,"D",IF(C5<65,"C",IF(C5<70,"C+",IF(C5<75,"B",IF(C5<80,"B+",IF(C5<85,"A-",IF(C5<90,"A","A+"))))))))`

**Note:** Maintain proper order while defining conditions (lowest to highest/highest to lowest)

### 2. Apply IF Function with Triple Conditions

Suppose you want to allocate some number of students in the thesis/project program. The conditions are,

**Condition 1:** The student has to obtain a **CGPA **of more than **2.50 (***must be fulfilled***)**

**And** **Condition 2:** He has to earn a total number of credits more than or equal to **110**,

**Or Condition 3: **He has to complete **at least 1** elective course.

Here, A student must fulfill the 1st condition. After fulfilling the 1st condition, either the 2nd or 3rd condition needs to be fulfilled to be eligible to take the thesis/project.

Now. write the below formula in Cell **F5 **and copy this formulated cell to the rest of column **F **where you want to find your result.

`=IF(AND(C5>=2.5,OR(D5>=110,E5>=1)), "Yes","No")`

**Formula Breakdown:**

- If
*all conditions*are matched then**the AND function**returns**TRUE**otherwise - If
*any condition*is matched then**the OR function**returns**TRUE**otherwise**FALSE** **AND(C5>=2.5,OR(D5>=110,E5>=1))**satisfied by the**IF**function then it returns**YES**otherwise**NO**.

By doing this you will get the desired result that you were looking for.

### 3. Use Multiple IF-AND Statements in Excel

Let`s work on an example where a patient with **HIV/AIDS** and the opportunistic disease *Tuberculosis *would first be sent to *Ward One*, to collect *ARVS* (Antiretrovirals) and then to the *respiratory* department of the hospital, to receive treatment and palliative care for *Tuberculosis*. A patient with **HIV/AIDS** and the opportunistic disease *Kap**osiâ€™s** sarcoma* would first be sent to *Ward One* to collect *ARVS* (Antiretrovirals) and then to the *oncology* department of the hospital, to receive treatment for *Kaposiâ€™s sarcoma**.*

A patient with HIV/AIDS and cardiovascular disease would first be sent to *Ward One* to collect *ARVS* (Antiretrovirals) and then to the cardiology department of the hospital, to receive treatment for cardiovascular disease. If the patient does not have **AIDS**, but one of the other opportunistic infections only or another disease, they bypass *Ward One* and go directly to the department they need. Letâ€™s follow the instructions below to learn!

**Step 1:**

- We now are going to use the
**IF**and**AND**functions in one complex formula, in order to determine the route each respective patient must take in the hospital. - In Cell
**E5**type the following formula:

`=IF(AND(C6="Yes",D6="Tuberculosis"), "Patient sent to Ward One, then respiratory department",IF(AND(C6="Yes",D6="Kaposi's sarcoma"),"Patient sent to Ward One, then oncology department",IF(AND(C6="Yes",D6="Cardiovascular disease"),"Patient sent to Ward One, then cardiology department","Bypass Ward One and go directly to department")))`

- After that, press
**CTRL-ENTER**to get the formula to deliver the route needed, given the conditions in Cell**C5**and**D5**that the formula is using for evaluation. We see that if the patient has*HIV/AIDS*and opportunistic infection of*Tuberculosis*, this patient must first go to*Ward One*and then to the*respiratory*department of the hospital. The formula has, therefore, delivered the correct result.

**Step 2:**

- Hence,
**AutoFill**the functions to the rest of the cells in column**E**. And there you have it, the correct route for each patient registered can identify by the formula.

**Read More: **How to Use Excel IF Between Multiple Ranges

## Things to Remember While Working With Multiple IFs

You need to be aware of the following things while working with multiple **IF **functions.

- In Excel 2007 â€“ 2016, a total of 64 conditions can nest up while working with multiple
**IFs**. - You have to maintain a proper order while working with multiple
**IFs**. - If your formula contains too many
**IFs**, itâ€™s better to use**OR**and**AND**functions with that. The**OR**and**AND**functions usually reduce the formula size. You can also use**the CONCATENATE function****.**If necessary,**VLOOKUP, LOOKUP,**and**INDEX/MATCH.**TheseÂ functions can also be used instead of multiple functions.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

If you have multiple criteria for different situations, itâ€™s often best to combine logical functions. Combining logical functions allows one to evaluate many conditions and deliver the needed output for the said conditions. Please feel free to comment below and tell us the highest number of criteria you have ever evaluated, using logical function combinations.

**Related Articles**

- How to Use Excel IF Function with Range of Values
- How to Use Multiple IF Statements with Text in Excel
- How to Write Greater Than or Equal To in Excel IF Function
- How to Use MAX IF Function in Excel
- If a Value Lies Between Two Numbers Then Return Result in Excel
- How to Check If a Value Is Between Two Numbers in Excel
- How to Make Yes 1 and No 0 in Excel
- How to Check If Value Exists in Range in Excel
- [Fixed!] IF Function Is Not Working in Excel

Hi Kawser Thanks for the blog.

I would like to make an adjustment herein . When the HIV/AIDS Condition is “No” in your sheet , the patient is requested to “Bypass Ward One and go directly to department”. Here, the name of the specific department is not mentioned (respiratory, cardiology or otherwise).

However if you modify the formula as follows, the patient is requested to bypass ward one and go to the specific department (respiratory, cardiology , Emphysema treatment):

=IF(AND(B5=”Yes”,C5=”Tuberculosis”),”Patient sent to Ward One, then respiratory department”,IF(AND(B5=”Yes”,C5=”Kaposi’s sarcoma”),”Patient sent to Ward One, then oncology department”,IF(AND(B5=”Yes”,C5=”Cardiovascular disease”),”Patient sent to Ward One, then cardiology department”,IF(AND(B5=”No”,C5=”Tuberculosis”),”By Pass ward one and go directly to respiratory department”,IF(AND(B5=”No”,C5=”Emphysema”),”Bypass Ward One and go directly to Emphysema treatment department”,IF(AND(B5=”No”,C5=”Kaposi’s sarcoma”),”Bypass ward one and go directly to oncology department”,”Bypass Ward one and directly go to cardiology department”))))))

Hope you got it :):):):)

Thank you for your contribution. The modification of the formula, you suggested, would work for additional detailed routes that the hospital in question, may feel they need to add.

We can lead on from this and design an Excel Sheet with VBA using Nested IFS and a small picture of the routes, for the ten most common routes patients at a certain hospital need to take based on the most often noted diseases and disease combinations. So I will include the routes you specified in the Visual Basic Application.

So look out for that post.

I am putting the same formula , so the place on No HIV/AIDS, its showing FALSE.

Why???

Syed,

Sometimes when the formula is posted in WordPress platform, it might be changed. Please, download the working file and check the formula there.

Dear Kawser,

Thanks for the blog.very nice and helpful.

regarding (If Function with 3 Conditions) I think the conditions should be like this:

The student has to obtain a CGPA more than 2.50.

He has to earn a total number of credits more than or equal to 110

He has to complete at least 1 elective courses

The student must fulfill the three conditions, not just the 1st and 2nd, or 1st and 3rd

Am I right?

regards

Hi Ahmad,

You’re welcome. Glad our blog posts help you master Excel topics.

I have updated this part. For your clarification: the condition 1 must be fulfilled to be eligible to take the thesis work/project. Of the last conditions, students must fulfill one condition. I hope this makes things clear to you.

Best regards

Kawser

Thanks a lot Mr kawser, now it is very clear, and thanks for the update.

Thanks a lot Mr. Kawer, now it is very clear.

also, thanks for the update.

regards

Ahmad