How to Use IF-THEN Statements with Text in Excel (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to use some combination of functions in Excel for searching a particular text or returning the answer as text. So, if you are looking for how to use the IF-THEN statements with text in Excel, then you have come to the right place. Today, I will explain how to use this statement to do various text in Excel.
Furthermore, for this session, I will use Microsoft Office 365.


How to Use IF-THEN Statements with Text in Excel: 7 Examples

Here, I will demonstrate 7 suitable methods with detailed steps on how to use IF-THEN statements with text in Excel. For your better understanding, I am going to use the following dataset. Which contains three columns. Those are Product, Color, and Size. The dataset is given below.

Dataset to Make IF Then Statements in Excel with Text


1. Combine IF, SEARCH, and ISNUMBER Functions to Find a Certain Text String

Here, I will combine IF, ISNUMBER, and SEARCH functions for searching a certain text string in Excel. Suppose you want to know how many products such as shirts or t-shirts are.
Now, if you notice then you will see that within both products “Shirt” is common. So, I will apply the formula based on “Shirt”. Now, follow the steps given below.

Steps: 

  • Firstly, write the following formula in the E5 cell.
=IF(ISNUMBER(SEARCH("shirt", B5)), "Found", "Not Found")
  • Secondly, press ENTER.

Combining Excel Functions to Create IF Then Statements with Text

Formula Breakdown

  • Here, the SEARCH function will search for the value “shirt” and return the position of this text within the B5 cell.
    • So, SEARCH(“shirt”, B5)—> returns 1.
  • Then, the ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Actually, I used this function because it checks whether the SEARCH function’s result is a number or not.
    • Thus, ISNUMBER(1)—> gives TRUE.
  • Lastly, the IF function will return a logical test result.
  • Here, “Found” —> when the logical test will be TRUE then it will return Found. Basically, an Inverted Comma is a must for getting a text as the output.
  • Then, “Not Found”  —> denotes that when the logic fails it will return Not Found.
  • Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Using Fill Handle Icon to Copy Formula

As a result, you will get how many products are shirts or t-shirts.


2. Merge IF, and EXACT Functions for Matching a Text String

Now, I will merge IF, and EXACT functions for creating an IF-THEN statement with text in Excel. Suppose you want to know how many products as Shirts are. So, I will apply the EXACT function. Now, follow the steps given below.

Steps: 

  • First, write the following formula in the E5 cell.
=IF(EXACT("Shirt", B5), "Found", "Not Found")
  • Then, press ENTER.

Merge IF & EXACT Functions to Make IF Then Statements with Text

Formula Breakdown

  • Here, the EXACT function will check whether the cell value of B5 is as same as “Shirt”.
  • Then, the IF function will return the logical test result.
  • So, if the EXACT function returns TRUE then the IF function will return “Found”.
  • Otherwise, the IF function will return “Not Found“.
  • Subsequently, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Finally, you will see how many products as Shirts are.

Applying Formula in All Cells

Note: If there is any value like “shirt” (where “s” is in smaller form), still the EXACT function will not consider this as same as “Shirt”. So, this one is a case-sensitive method.

3. Employing IF Function Only to Get Status of Availability

Here, I will use only the IF function to make an IF-THEN statement with text. Suppose you want to know how many products as Shirts are only. Now, I will show you how you can do this by using only the IF function. So, follow the steps given below.

Steps: 

  • Firstly, write the following formula in the E5 cell.
=IF(B5="shirt", "Found", "Not Found")
  • Secondly, press ENTER.

Employing IF Function as an IF Then Statement with Text in Excel

Formula Breakdown

Here, the IF function will return the output of a logical test.

  • Firstly, B5=”shirt” denotes the logical test. This test will check whether the cell value of B5 is equal to shirt or not.
  • Secondly, “Found” —> when the cell value of B5 is equal to shirt then it will return Found. Here, an Inverted Comma is a must for getting a text as the output.
  • Lastly,“Not Found”  —> denotes that when the logic fails then it will return Not Found.
  • Consequently, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

As a result, you will know how many products contain shirts.

Using IF Function to Know the Status of a Range

Note:  Here, this one is a case-insensitive method. So, when there is any value like “Shirt” (where “S” is in capital form), it will consider this as same as “shirt” and gives Found as output.

4. Unite IF and ISTEXT Functions

In this method, I will unite the IF, and ISTEXT functions to prepare IF-THEN statements with text in Excel.
Suppose you want to check whether the values in the Size column are right or wrong. So, if the Size column contains any numerical value, then it will not be correct as the value should be alphabetic like XL, XXL, S, M, L, or XS. Thus, I will use the ISTEXT function here.
Now, let’s follow the steps given below.

Steps: 

  • Firstly, write the following formula in the E5 cell.
=IF(ISTEXT(D5),"OK","Wrong Info")
  • Secondly, press ENTER.

Unite IF and ISTEXT Functions for Making IF Then Statements with Text

Formula Breakdown

  • Here, the ISTEXT function will check whether the cell value of B5 is a text or not.
  • Then, the IF function will return “Found” when the ISTEXT function returns TRUE.
  • Else, the IF function will return “Not Found“.
  • After that, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Lastly, you will get Wrong Info as the status if the size of any product is not correct.

Know the Status of Products by Applying IF & ISTEXT Functions


5. Combining OR Function with IF-THEN Statements to Find a Certain Text in Excel

You may combine IF, OR, ISNUMBER, and SEARCH functions for searching a text string in Excel.
Suppose you want to know if is there any product which will be a shirt or having any shade of green color.
Here, if you notice then you will see that there is an OR logical test. So, I will apply the formula based on “shirt or green”. Now, follow the steps given below.

Steps: 

  • First, write the following formula in the E5 cell.
=IF(OR(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")
  • Subsequently, press ENTER.

Combining OR and IF Then Statements with Text in Excel

Formula Breakdown

  • Firstly, the OR function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)).
  • Secondly, B5=”shirt” will check whether the cell value of B5 is equal to the shirt or not.
    • B5=”shirt”—> gives TRUE.
  • Thirdly, the SEARCH function will search for the value “green” and return the position of this text within the C5 cell.
    • So, SEARCH(“green”, C5)—> returns #VALUE!.
  • Fourthly, the ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Actually, I used this function because it checks whether the SEARCH function’s result is a number or not.
    • Thus, ISNUMBER(#VALUE!)—> gives FALSE.
  • Furthermore, the OR function will return TRUE when any of the logic becomes TRUE.
    • OR(TRUE, FALSE)—> turns TRUE.
  • Lastly, the IF function will return Found when the logical test will be TRUE otherwise it will return Not Found.
  • Then, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

6. Merging AND Function with IF-THEN Statements to Search for a Text String

Here, I will unite IF, AND, ISNUMBER, and SEARCH functions for making an IF-THEN statement with AND logic.
Suppose you want to know if is there any product which will be a shirt of any shade of green color.
Here, if you notice then you will see that you should use an AND logical test. So, I will apply the formula based on “shirt and green”. Now, follow the steps given below.

Steps: 

  • Firstly, write the following formula in the E5 cell.
=IF(AND(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")
  • Secondly, press ENTER.

Combining AND and IF Then Statements with Text in Excel

Formula Breakdown

  • Firstly, the AND function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)).
  • Secondly, B5= “shirt” will check whether the cell value of B5 is equal to the shirt or not.
    • B5= “shirt”—> gives TRUE.
  • Thirdly, the SEARCH function will search for the value “green” and return the position of this text within the C5 cell.
    • So, SEARCH(“green”, C5)—> returns #VALUE!.
  • Fourthly, the ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. Actually, I used this function because it checks whether the SEARCH function’s result is a number or not.
    • Thus, ISNUMBER(#VALUE!)—> gives FALSE.
  • Furthermore, the AND function will return TRUE when both logic becomes TRUE.
    • AND(TRUE, FALSE)—> turns FALSE.
  • Lastly, the IF function will return Found when the logical test will be TRUE otherwise it will return Not Found.
  • After that, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

As a result, you will find which products are shirts with any shade of green color.

Merge IF, AND, ISNUMBER, & SEARCH Functions


7. Multiple IF-THEN Statements to Get Logical Output Depending on Various Conditions

Here, I will employ multiple IF functions for preparing IF-THEN statements with text in Excel. Suppose you want to know if any product of shirt is there having sea green color and L size.
Now, follow the steps given below.

Steps: 

  • Firstly, write the following formula in the E5 cell.
=IF(B5="shirt",IF(C5="sea green",IF(D5="L","Found")))
  • Secondly, press ENTER.

Multiple IF Statements with Text in Excel

Formula Breakdown

  • Here, I have used a combination of three IF functions.
  • Firstly, IF(D5=”L”, “Found”) will check whether the cell value of D5 is equal to L or not. If the value is equal to L then it will return Found otherwise return FALSE.
    • Output: FALSE.
  • Secondly, IF(C5=”sea green”, FALSE) will check whether the cell value of C5 is equal to sea green or not. If the value is equal to sea green then it will return FALSE, which is based on the previous logic.
    • Output: FALSE.
  • Lastly, IF(B5= “shirt”, FALSE) will check whether the cell value of B5 is equal to the shirt or not. If the value is equal to the shirt then it will return FALSE, which is based on the above logic.
    • Output: FALSE.
  • So, this is kind of a chain logical test, if one fails means you will get FALSE as output.
  • Finally, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column and you will get the shirt having sea green color with L size.

Use the IF function with different conditions


Practice Section

Now, you can practice the explained methods by yourself.

Practice Section to Make IF Then Statements with Text in Excel


Download Practice Workbook


Conclusion

I hope you found this article helpful. Here, I have described how to use the IF-THEN Statement in Excel with Text. Please, drop comments, suggestions, or queries if you have any in the comment section below.


<< Go Back to Text | If Cell Contains | Formula List | 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.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo