To demonstrate how to use **IF-THEN statements with text** in Excel, we will use the following dataset, which contains columns for **Product, Color,** and** Size**:

### Method 1 – Combining IF, SEARCH, and ISNUMBER Functions to Find a Certain Text String

Suppose you want to know how many products such as **shirts** or **t-shirts** are in our dataset. The text “**Shirt**” is common to both products, so we apply the formula based on the text “**Shirt**”.

**STEPS:**

- Enter the following formula in cell
**E5**:

`=IF(ISNUMBER(SEARCH("shirt", B5)), "Found", "Not Found")`

- Press
**ENTER**.

**Formula Breakdown**

**The SEARCH function**will search for the value**“shirt”**and return the position of this text within cell**B5**.- So,
**SEARCH(“shirt”, B5)—>**returns**1**.

- So,
**The ISNUMBER function**returns**TRUE**when a cell contains a number, and**FALSE**if not. Here it checks whether the**SEARCH**function’s result is a number or not.- Thus,
**ISNUMBER(1)—>**returns**TRUE**.

- Thus,
**The IF function**will return a logical test result. Here,**“Found” —>**when the**logical test**is**TRUE**it will return “**Found**“.*Basically, an***Inverted Comma**is a must for getting a text as the output.**“Not Found” —>**denotes that when the logic fails it will return “**Not Found**“.

- Drag the
**Fill Handle**down to copy the formula to the other cells of the column.

The number of products that are **shirts **or** t-shirts** is returned.

### Method 2 – Merging IF, and EXACT Functions to Match a Text String

Suppose you want to know how many products are **Shirts**.

**Steps: **

- Enter the following formula in cell
**E5**.

`=IF(EXACT("Shirt", B5), "Found", "Not Found")`

- Press
**ENTER**.

**Formula Breakdown**

**The EXACT function**will check whether the cell value of**B5**is as same as**“Shirt”**.**The IF function**will return a logical test result. If**the EXACT function**returns**TRUE**then the**IF**function will return “**Found**”. Otherwise,**the IF function**will return “**Not Found**“.

- Drag the
**Fill Handle**icon to copy the formula to the other cells of the column.

The number of products that are **Shirts** is returned.

**Note:**This is a

**case-sensitive**method, so if there is a value like “

**shirt**” (where “

**s**” is in lowercase format),

**the EXACT function**will not consider this as same as “

**Shirt**”.

### Method 3 Using IF Function to Get Status of Availability Only

Suppose you want to know how many products are **Shirts** only.

**Steps: **

- Enter the following formula in cell
**E5**:

`=IF(B5="shirt", "Found", "Not Found")`

- Press
**ENTER**.

**Formula Breakdown**

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

**B5=”shirt”**denotes the logical test, which will check whether the cell value of**B5**is equal to**shirt**or not.**“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.**“Not Found” —>**when the logic fails then it will return**Not Found**.

- Drag the
**Fill Handle**icon to copy the formula to the other cells of the column.

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

**Note:**This is a

**case-insensitive**method. So when there is a value like “

**Shirt**” (where “

**S**” is in uppercase format), it will consider this as same as “

**shirt**” and return

**Found**as output.

### Method 4 – Combining IF and ISTEXT Functions

Suppose you want to check whether the values in the **Size** column are correct. If the **Size** column contains a **numerical** value, then it will not be correct as the value should be **alphabetic** like XL, XXL, S, M, L, or XS. We will use **the ISTEXT function** to perform this task.

**Steps: **

- Enter the following formula in cell
**E5**:

`=IF(ISTEXT(D5),"OK","Wrong Info")`

- Press
**ENTER**.

**Formula Breakdown**

- Here,
**the ISTEXT function**will check whether the cell value of**B5**is text or not**.** **The IF function**will return “**Found**” when**the ISTEXT function**returns**TRUE**, else**the IF function**will return “**Not Found**“.

- Drag the
**Fill Handle**icon to copy the formula to the other cells of the column.

**Wrong Info** will be returned as the status if the size of any product is not correct.

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

Suppose you want to know if there is any products which is a **shirt ***or* has any shade of the color** green**.

**Steps: **

- Enter the following formula in cell
**E5**:

`=IF(OR(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")`

- Press
**ENTER**.

**Formula Breakdown**

**The OR function**will consider**two**logical tests. One is**B5=”shirt”**and other is**ISNUMBER(SEARCH(“green”,C5))**.**B5=”shirt”**will check whether the cell value of**B5**is equal to**shirt**or not.**B5=”shirt”—>**returns**TRUE**.

**The SEARCH function**will search for the value**“green”**and return the position of this text within cell**C5**.- So,
**SEARCH(“green”, C5)—>**returns**#VALUE!.**

- So,
**The ISNUMBER function**returns**TRUE**when a cell contains a number, and**FALSE**if not. Here it checks whether the**SEARCH**function’s result is a number or not.- Thus,
**ISNUMBER(#VALUE!)—>**returns**FALSE**.

- Thus,
**The OR function**will return**TRUE**when any of the logic becomes**TRUE**.**OR(TRUE, FALSE)—>**returns**TRUE**.

**The IF function**will return**Found**when the**logical test**is**TRUE**otherwise it will return**Not Found**.

- Drag the
**Fill Handle**icon to copy the formula to the other cells of the column.

### Method 6 – Merging AND Function with IF-THEN Statements to Search for a Text String

Suppose you want to know if is there any product which is both a **shirt ***and* any shade of the color** green**.

**Steps: **

- Enter the following formula in cell
**E5**:

`=IF(AND(B5="shirt",ISNUMBER(SEARCH("green",C5))),"Found", "Not Found")`

- Press
**ENTER**.

**Formula Breakdown**

**The AND function**will consider**two**logical tests. One is**B5=”shirt”**and other is**ISNUMBER(SEARCH(“green”,C5)).****B5= “shirt”**will check whether the cell value of**B5**is equal to**shirt**or not.**B5= “shirt”—>**returns**TRUE**.

**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!.**

- So,
**The ISNUMBER function**returns**TRUE**when a cell contains a number, and**FALSE**if not. Here it checks whether the**SEARCH**function’s result is a number or not.- Thus,
**ISNUMBER(#VALUE!)—>**returns**FALSE**.

- Thus,
**The AND function**will return**TRUE**when**both**logical tests are**TRUE**.**AND(TRUE, FALSE)—>**turns**FALSE**.

**The IF function**will return**Found**when the**logical test**is**TRUE**otherwise it will return**Not Found**.

- Drag the
**Fill Handle**icon to copy the formula to the other cells of the column.

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

### Method 7 – Multiple IF-THEN Statements to Get Logical Output Depending on Various Conditions

Suppose you want to know if any **shirt** has **sea green** color *and* **L** size.

**Steps: **

- Enter the following formula in cell
**E5**:

`=IF(B5="shirt",IF(C5="sea green",IF(D5="L","Found")))`

- Press
**ENTER**.

**Formula Breakdown**

- Here, we have used a combination of
**three****IF**functions. **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**FALSE**.**Output: FALSE**.

**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**, based on the previous logic.**Output: FALSE**.

**IF(B5= “shirt”, FALSE)**will check whether the cell value of**B5**is equal to**shirt**or not. If the value is equal to**shirt**then it will return**FALSE**, based on the above logic.**Output: FALSE**.

- So, this is a kind of
**chain logical test**, where if only one test fails you will get**FALSE**as output.

- Drag the
**Fill Handle**icon to copy the used formula**respectively**to the other cells of the column.

Returned will be the **shirt** having **sea green** color with** L **size.

**Download Practice Workbook**

**<< Go Back to Text | If Cell Contains | Formula List | Learn Excel**