How to Use Multiple IF Statements with Text in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Note

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.

Use AND Condition with IF Function for Case-Insensitive Match

  • Then, drag the AutoFill Handle to copy this formula to the rest of the cells.

  • You will get the result for all the cells.

Using IF and AND functions to use multiple if statements in excel with text

Thus, we can say that our formula works perfectly, and we are able to use multiple IF statements with text in Excel.

Notes
  • 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.

Applying AND Condition with IF Function for Case-Sensitive Match

  • 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.

Applying IF, AND, and EXACT functions to multiple if statements in excel with text

Hence, we can say that our formula works effectively, and we are able to use multiple IF statements for multiple conditions in Excel.

Notes
  • 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.

Utilizing OR Condition with IF Function for Case-Insensitive Match

  • Then, drag the AutoFill Handle to copy this formula to the rest of the cells.

  • You will figure out the results.

Utilizing IF, OR and EXACT functions to use multiple if statements in excel with text

Therefore, we can say that our formula works precisely, and we are able to use multiple IF statements for multiple conditions in Excel.

Notes
  • 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.

Apply OR Condition with IF Function for Case-Sensitive Match

  • Drag the AutoFill Handle to copy this formula to the rest of the cells.

  • You will see the result in column E.

Using IF, OR and EXACT function to use multiple if statements in Excel with text

At last, we can say that our formula works fruitfully, and we are able to use multiple IF statements for multiple conditions in Excel.

Formula Breakdown
  • 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.

Using Nested IF Statements for Multiple Conditions

  • Then, drag the AutoFill Handle to copy this formula to the rest of the cells.

  • You will figure out the result.

Using IF formula to use multiple statements

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.

Notes
  • 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.

Multiple IF Statements with Array Formula for Condition Range to Use Multiple If Statement with Text

  • 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.

Notes
  • 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. 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.

    • Reply Avatar photo
      Naimul Hasan Arif Oct 10, 2022 at 5:11 PM

      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] 

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo