Many a time while working in Excel, we have to work with multiple IF statements to add different conditions or criteria. In this article, I’ll show you how you can use multiple IF statements with text in Excel with six quick examples. If you are also curious to know about it, download our practice workbook and follow us.
How to Use Multiple IF Statements with Text in Excel: 6 Practical Examples
Here we’ve got a data set with the examination record of some students of a school. Today our objective is to find out the overall result of each student based on their results in Physics and Chemistry. We shall apply multiple IF statements here.
All the operations of this article are accomplished by using the Microsoft 365 application.
1. Use AND Condition with IF Function for Case-Insensitive Match
Let’s think for a moment that the overall result of a student is “Pass” only if they pass in both subjects, otherwise, it is “Fail”. Here, we will have to apply the AND function within the IF function. The steps of this method are given below:
📌 Steps:
- First of all, select cell E5.
- Now, write down the following formula in the cell.
=IF(AND(C5="pass",D5="pass"),"Pass","Fail")
- Hence, press Enter.
- Then, drag the AutoFill Handle to copy this formula to the rest of the cells.
- You will get the result for all the cells.
Thus, we can say that our formula works perfectly, and we are able to use multiple IF statements with text in Excel.
- The IF function matches case-insensitive matches by default. So whether you use C4=“pass” or C4=“Pass” doesn’t matter here.
- AND(C4=”pass”,D4=”pass”) returns TRUE only if both conditions are TRUE. Otherwise, it returns FALSE.
- Therefore, IF(AND(C4=”pass”,D4=”pass”),”Pass”,”Fail”) will return “Pass” only if he/she passes in both the subjects, otherwise, it will return “Fail”.
2. Applying AND Condition with IF Function for Case-Sensitive Match
The IF function by default returns case-insensitive matches with texts. So, if you want to return a case-sensitive match, you have to be a bit tricky. You can use the Exact function of Excel in combination with the IF function to return case-sensitive matches. Besides the IF function, we will use the AND and EXACT functions. The steps of this example are given as follows:
📌 Steps:
- First, select cell E5.
- After that, write down the following formula in the cell.
=IF(AND(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")
- Then, press Enter.
- Now, drag the AutoFill Handle to copy this formula to the rest of the cells in column E.
- You will see all the results will appear in our desired column.
Hence, we can say that our formula works effectively, and we are able to use multiple IF statements for multiple conditions in Excel.
- The EXACT function works with case-sensitive matches. So you have to use exactly EXACT(C4,”Pass”).
- EXACT(C4,”pass”) will not work here. It will return FALSE. You can test it yourself.
- The rest is like the previous formula. IF(AND(EXACT(C4,”Pass”),EXACT(D4,”Pass”)),”Pass”,”Fail”) will return “Pass” only if there is “Pass” in both the subjects.
3. Utilizing OR Condition with IF Function for Case-Insensitive Match
Now, we will apply the OR function within the IF function. Let’s think for this moment that any student passes the examination if he/she passes at least one subject in the examination. Therefore, to find out the overall result of the students, we have to apply the OR condition. The steps of this procedure are shown below:
📌 Steps:
- At first, select cell E5.
- Next, write down the following formula in the cell.
=IF(OR(C5="pass",D5="pass"),"Pass","Fail")
- Now, press Enter.
- Then, drag the AutoFill Handle to copy this formula to the rest of the cells.
- You will figure out the results.
Therefore, we can say that our formula works precisely, and we are able to use multiple IF statements for multiple conditions in Excel.
- The IF function matches case-insensitive matches by default. So whether you use C4=“pass” or C4=“Pass” doesn’t matter here.
- OR(C4=”pass”,D4=”pass”) returns TRUE if at least one of the conditions is TRUE. Otherwise, it returns FALSE.
- Therefore, IF(OR(C4=”pass”,D4=”pass”),”Pass”,”Fail”) will return “Pass” if he/she passes in at least one subject, otherwise it will return “Fail”.
4. Apply OR Condition with IF Function for Case-Sensitive Match
Like the one that we used in AND condition, you can use a combination of the EXACT and IF functions to generate a case-sensitive match. The steps of this procedure are shown below:
📌 Steps:
- Firstly, select cell E5.
- Afterward, write down the following formula in the cell.
=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")
- Further, press Enter.
- Drag the AutoFill Handle to copy this formula to the rest of the cells.
- You will see the result in column E.
At last, we can say that our formula works fruitfully, and we are able to use multiple IF statements for multiple conditions in Excel.
- The EXACT function works with case-sensitive matches. So you have to use exactly EXACT(C4,“Pass”).
- EXACT(C4, “pass”) will not work here. It will return FALSE. You can test it yourself.
- The rest is like the previous formula. IF(OR(EXACT(C4,”Pass”),EXACT(D4,”Pass”)), ”Pass”,”Fail”) will return “Pass” if there is “Pass” in at least one subject.
5. Using Nested IF Statements for Multiple Conditions
Up till now, we have used the AND, and OR functions within the IF function to handle multiple criteria. But you can also use an IF function within another IF function to deal with multiple criteria. This is called multiple IF statements. The AND statement, that is, a student will pass only if he/she passes in both subjects, can be also implemented with nested IF statements. The procedure is explained below step-by-step:
📌 Steps:
- At the beginning of the process, select cell E5.
- Now, insert the following formula in the cell.
=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")
- After that, press Enter.
- Then, drag the AutoFill Handle to copy this formula to the rest of the cells.
- You will figure out the result.
In the end, we can say that our formula works fruitfully, and we are able to use multiple IF statements for multiple conditions with text in Excel.
- Here, if the value in cell C4 is “Pass”, then it will move to see what the value in cell D4 is.
- If the value in cell D4 is also “Pass’, only then it will certify as “Pass”. Otherwise, it will certify as “Fail”.
- And the IF function returns a case-insensitive match. So C4=”pass” or C4=”Pass” really doesn’t matter here.
Read More: How to Use IF Function with Multiple Conditions in Excel
6. Multiple IF Statements with Array Formula for Condition Range
All we have done up to this point is that we have applied the formula in the first cell and then dragged the AutoFill Handle to fill the formula to the rest of the cells. But, you can also use an Array Formula to fill all the cells together. The AND and OR formula that we used earlier can’t be applied to the Array formula. But, you can apply an Array formula with the nested IF function. The steps of this approach are given below:
📌 Steps:
- At the start, select cell E5.
- Now, write down the following formula in the cell.
=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")
- After that, press Enter.
- You will figure out the result in our desired area.
At last, we can say that our formula works fruitfully, and we are able to use multiple IF statements for multiple conditions in Excel.
- Here, C4:C13 and D4:D13 are the two ranges of my criteria. You use your one.
- Here, we are opting for a case-sensitive match. If you want a case-insensitive match, use C4:C13=“Pass” and D4:D13=“Pass” instead.
- Press CTRL+SHIFT+ENTER to enter the formula unless you are in Office 365.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to use multiple IF statements with text in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Related Articles
<< Go Back to Excel IF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
HI, I want to pull text from multiple sheets and ignore some specific text as blank or space. How to do it?
Example .
If sheet 1 has John in B2 and sheet2 has Jacob in B2 and i want an output in sheet3, where if sheet 2 has Jacob then ignore or blank.
Thanks for your query.
It’s kind of a complicated task to filter specific data from certain cells of different worksheets with certain condition. I have tried a possible simple solution to pull data from different sheets into one sheet using FILTER function.I have used the following dataset for filtering the rows having Geller as Last Name and replace the Geller word with blank.
I have used the following formula to fulfill the purpose.
=FILTER(IF(Dataset!B4:J17=”Geller”,””,Dataset!B4:J17),Dataset!C4:C17=”Geller”)
For further information related to Excel, you can send message via email. email id: [email protected]