How to Use Multiple IF Statements with Text in Excel (6 Methods)

Below is a dataset with the examination records of some school students. Our objective is to find the overall result of each student based on their results in Physics and Chemistry.

Note


Method 1 – Using the AND Condition with IF Function for Case-Insensitive Match

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(AND(C5="pass",D5="pass"),"Pass","Fail")

  • Press Enter.

Use AND Condition with IF Function for Case-Insensitive Match

  • 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

Code Breakdown

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


Method 2 – Applying the AND Condition with the IF Function for Case-Sensitive Match

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(AND(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")

  • Press Enter.

Applying AND Condition with IF Function for Case-Sensitive Match

  • 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

Code Breakdown

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.


Method 3 – Utilizing the OR Condition with the IF Function for Case-Insensitive Match

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(OR(C5="pass",D5="pass"),"Pass","Fail")

  • Press Enter.

Utilizing OR Condition with IF Function for Case-Insensitive Match

  • 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

Code Breakdown

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


Method 4 – Applying the OR Condition with the IF Function for Case-Sensitive Match

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")

  • 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

Code Breakdown

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.


Method 5 – Using Nested IF Statements for Multiple Conditions

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")

  • Press Enter.

Using Nested IF Statements for Multiple Conditions

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

You will see the result.

Using IF formula to use multiple statements

Code Breakdown

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


Method 6 – Multiplying the IF Statements with Array Formula for Condition Range

Steps:

  • Select cell E5.
  • Enter the following formula in the cell:

=IF(OR(EXACT(C5,"Pass"),EXACT(D5,"Pass")),"Pass","Fail")

  • Press Enter.

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

You will see the result in our desired area.

Code Breakdown

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 the Practice Workbook

Download this workbook for practice.


Related Articles


<< Go Back to Excel IF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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