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

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.


Download Practice Workbook


6 Quick Methods to Use Multiple IF Statements with Text in Excel

Here we’ve got a data set with the examination record of some students in Physics and Chemistry in a school called Sunflower Kindergarten.

Data Set to Use Multiple IF Statements in Excel with Text

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.


1. Multiple IF Statements with Text with AND Condition (Case-Insensitive Match)

Let’s think for a moment that the overall result of a student is “Pass” only if they pass in both the subjects, otherwise it is “Fail”.

Here we will have to apply an AND function within the IF function.

Therefore, the formula for the overall result of the first student will be:

=IF(AND(C4="pass",D4="pass"),"Pass","Fail")

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

AND Function to Insert Multiple IF Statements in Excel with Text

Now drag the Fill Handle to copy this formula to the rest of the cells.

Dragging the Fill Handle to Use Multiple IF Statements in Excel with Text


2. Multiple IF Statements with Text with AND Condition (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.

Use this formula for the overall result of the first student:

=IF(AND(EXACT(C4,"Pass"),EXACT(D4,"Pass")),"Pass","Fail")

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.

Case-Sensitive Match to Use Multiple IF Statements in Excel with Text

Now, you can drag the Fill Handle to copy this formula to the rest of the cells.

Dragging the Fill Handle to Use Multiple IF Statements in Excel with Text


3. Multiple IF Statements with Text with OR Condition (Case-Insensitive Match)

Now we will apply OR function within the IF function.

Let’s think for this moment that any student passes in the examination if he/she passes in 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 formula for the overall result of the first student will be:

=IF(OR(C4="pass",D4="pass"),"Pass","Fail")

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

OR Function to Use Multiple IF Statements in Excel with Text

Now drag the Fill Handle to copy this formula to the rest of the cells.

Dragging the Fill Handle to Use Multiple IF Statements in Excel with Text


4. Multiple IF Statements with Text with OR Condition (Case-Sensitive Match)

Like the one that we used in AND condition, you can use a combination of the EXACT function and the IF function to generate a case-sensitive match.

Use this formula for the first student:

=IF(OR(EXACT(C4,"Pass"),EXACT(D4,"Pass")),"Pass","Fail")

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(OR(EXACT(C4,”Pass”),EXACT(D4,”Pass”)),”Pass”,”Fail”) will return “Pass” if there is “Pass” in at least one subject.

Case-Sensitive Match to Use Multiple IF Statements in Excel with Text

Then drag the Fill Handle to copy the formula to the rest of the cells.

Dragging the Fill Handle to Use Multiple IF Statements in Excel with Text

Related Content: How to Use Excel IF Function with Range of Values


5. Nested IF Statements

Up till now, we have used the AND function and the OR function 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 the subjects, can be also implemented with nested IF statements in this way:

=IF(C4=”pass”,IF(D4=”pass”,”Pass”,”Fail”),”Fail”)

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.

Nested IF Statements to Use Multiple IF Statements in Excel with Text

Then drag the Fill Handle to fill this formula to the rest of the cells.

Dragging the Fill Handle to Use Multiple IF Statements in Excel with Text

Also, if you want a case-sensitive match, you can use the combination of the EXACT function and the IF function as shown earlier.

Use this formula in the first cell and then drag the Fill Handle.

Nested IF Formula to Use Multiple IF Statements in Excel with Text

Related Content: How to Use Excel IF Function with Range of Values


6. Multiple IF Statements with Array Formula

All we have done up to this point is that we have applied the formula in the first cell and then dragged the Fill 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.

To find out the overall result of all the students with an Array Formula, you can insert this formula:

=IF(EXACT(C4:C13,"Pass"),IF(EXACT(D4:D13,"Pass"),"Pass","Fail"),"Fail")

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.

Array Formula to Insert Multiple IF Statements in Excel with Text


Conclusion

Using these methods, you can use multiple IF statements with text in Excel. Do you know any other method? Or do you have any questions? Feel free to ask us.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo