Excel is an outstanding data analysis and visualization tool. Sometimes we require a value returned in a specific cell if that cell contains the text for data analysis or visualization purposes. As a result, this article will demonstrate 8 easy ways to return the value if the cell contains text in Excel.

**📁 Download Excel File**

Download the Excel file used for the demonstration from the link below.

## Learn to Return Value If Cell Contains Text in Excel with These 8 Ways

This article employs distinct functions to return the value if the cell contains the text. We have used **IF**, **VLOOKUP**, **EXACT**, **COUNTIF**, **AND**, **OR**, **ISNUMBER**, **SEARCH**, **INDEX**, and **MATCH **functions. Let’s explore the Excel formulae and ways for returning the value if the cell contains a text.

**Approach**

**1. Using IF Function**

**IF function** logically compares a value and your desired outcome and can return two results: one is if **TRUE, **and another one is **FALSE**. This method will use the **IF** function to return a value if the cell contains a text. For that, we have a data set containing **the Product Name** and the **City Name** as the delivery place. Now we want to know if it will be delivered to **California**.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, insert the formula in cell
**D6**.

**=IF(C6=”California”,”Yes”,”No”)**

Here, this part **C6=”California” **is the logical test part that compares the text string in cell **D6** with **California**. If the text string in cell **D6** matches, it returns **“Yes”** and otherwise, **“No”**.

- Then drag the
**Fill Handle**down to the last row and get the result.

**Approach**

**2. Applying VLOOKUP Function**

**VLOOKUP function **finds something in a table or a range by row. This method will use this function to return a value if a cell contains a text. Here we will try to find the delivery place of a specific product using this function.Let us see go through the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**C13**and enter the formula.

**=VLOOKUP(C12,B6:C10,2,FALSE)**

In this function, the first **C12** contains the value that we have to look up. **B6:C10 **is the array where the function will search for the lookup value, and 2 shows that we have to look up in the second column of the array, and **FALSE** represents that it needs to be an exact match.

**📕 Read More: VLOOKUP If Cell Contains a Word Within Text in Excel**

**Approach**

**3. Utilizing INDEX and MATCH Functions**

This method will utilize **INDEX** and **MATCH** functions to return value if the cell contains a text. Here we will try to find the delivery place of a specific product using this function.

Let us see go through the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**C13**and enter the formula.

**=INDEX(C6:C10,MATCH(C12,B6:B10,0))**

**🔨 Formula Breakdown**

Here, the formula inserted in **C13** is:

**INDEX(C6:C10,MATCH(C12,B6:B10,0))**

`👉`

Here **MATCH** function searches for a value in **C12** in the range **B6:B10** and returns the relative position of the item.

`👉`

On the other hand, **INDEX** function returns the value in the range **C6:C10** for the specified reference in the **MATCH** function.

**Approach**

**4. Employing IF and COUNTIF Functions**

**IF function** logically compares a value and your desired outcome and can return two results: one is if **TRUE, **and another one is **FALSE**. On the other hand, **the COUNTIF function** counts the number of cells that meets a specific criterion. This method will use the **IF** and **COUNTIF **functions to return a value if a cell contains a text. For that, we have a data set containing **the Product Name** and the **City Name** as the delivery place. Now we want to know if it will be delivered to **California**.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, insert the formula in cell
**D6**.

**=IF(COUNTIF(C6,”*California*”),”Yes”,”No”)**

**🔨 Formula Breakdown**

Here, the formula inserted in **D6** is:

**IF(COUNTIF(C6,”*California*”),”Yes”,”No”)**

`👉`

**COUNTIF(C6,”*California*”) **part counts the number of cells that contains “**California**”.

`👉`

Now, if **COUNIF** function returns a result then **IF **function returns **“Yes”** and otherwise **“No”**.

- After then, drag the
**Fill Handle**down to the final row to obtain the outcome.

**Approach**

**5. Using ISNUMBER and SEARCH Functions**

**The ISNUMBER function** checks a number value and returns **TRUE **or **FALSE**. On the other hand, **SEARCH function** searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.This method will demonstrate how to use these two functions to return value if a cell contains a text.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6**and insert the formula and we will have our outcome.

**=ISNUMBER(SEARCH(“California”,$C$6:$C$10))**

**🔨 Formula Breakdown**

Here, the formula inserted in **D6** is:

**ISNUMBER(SEARCH(“California”,$C$6:$C$10))**

`👉`

**SEARCH(“California”,$C$6:$C$10) **searches “**California**” in the range **$C$6:$C$10**.

`👉`

Then, the **ISNUMBER** function returns **TRUE** or **FALSE** depending on the output of **SEARCH** function.

**Approach**

**6. Applying IF and EXACT Functions**

**IF function** logically compares a value and your desired outcome and can return two results: one is if **TRUE, **and another one is **FALSE**. On the other hand, **the** **EXACT function** returns **TRUE** comparing two text strings if they match exactly; FALSE otherwise. This method will use the **IF and** **EXACT **functions to return a value if a cell contains a text. Let us have a look at the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, insert the formula in cell
**D6**.

**=IF(EXACT(C6,”New York”),”Yes”,”No”)**

**🔨 Formula Breakdown**

Here, the formula inserted in **D6** is:

**IF(EXACT(C6,”New York”),”Yes”,”No”)**

`👉`

**EXACT(C6,”New York”) **compares text in cell **C6** with “**New York**”.

`👉`

Now, **IF **function returns **“Yes”** if the **EXACT** function finds an exact match; otherwise, **“No”**.

- Lastly, drag the
**Fill Handle**down to the final row to obtain the results.

**Approach**

**7. Combining IF with OR Function**

**IF function** logically compares a value and your desired outcome and returns **TRUE, **or **FALSE**. **The OR function** determines if any conditions in a test are **TRUE**. In addition, **the ISNUMBER function** checks a number value and returns **TRUE **or **FALSE**. On the other hand, **SEARCH function** searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.

In this method, we will use all of these functions to return value if cell contains text. Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6**and insert the formula.

**=IF(OR(ISNUMBER(SEARCH(“California”,C6)),ISNUMBER(SEARCH(“Texas”,C6))),”Yes”,”No”)**

**🔨 Formula Breakdown**

Here, the formula inserted in **D6** is:

**IF(OR(ISNUMBER(SEARCH(“California”,C6)),ISNUMBER(SEARCH(“Texas”,C6))),”Yes”,”No”)**

`👉`

**SEARCH(“California”,C6) **searches “**California**” in the cell **C6** and **SEARCH(“Texas”,C6) **searches “**Texas**” in the cell **C6**.

`👉`

Then, the **ISNUMBER** function returns **TRUE** or **FALSE** depending on the output of **SEARCH** function.

`👉`

Next, **OR** function returns output for both arguments if they are **TRUE**.

`👉`

Lastly, **IF **function shows** “Yes**” or “**No**” according to the output from **OR** function.

- Finally, to get the outcome, drag the
**Fill Handle**down to the last row.

**Approach**

**8. Combining IF with AND Function**

**The ****IF function** logically compares a value and your desired outcome and returns **TRUE, **or **FALSE**. **The AND function** determines if all conditions in a test are **TRUE**. In addition, **the ISNUMBER function** checks a number value and returns **TRUE **or **FALSE**. On the other hand, **SEARCH function** searches the text string in another text string and returns the number of the starting position of the first text string from the first character of the second text string.

In this method, we will use all of these functions to return a value if a cell contains a text. Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**D6**and insert the formula.

**=IF(AND(ISNUMBER(SEARCH(“New York”,C6)),ISNUMBER(SEARCH(“New”,C6))),”Yes”,”No”)**

**🔨 Formula Breakdown**

Here, the formula inserted in **D6** is:

**IF(AND(ISNUMBER(SEARCH(“New York”,C6)),ISNUMBER(SEARCH(“New”,C6))),”Yes”,”No”)**

`👉`

**SEARCH(“New York”,C6) **searches “**New York**” in the cell **C6** and **SEARCH(“New”,C6) **searches “**New**” in the cell **C6**.

`👉`

Then, the **ISNUMBER** function returns **TRUE** or **FALSE** depending on the output of **SEARCH** function.

`👉`

Next, **AND** function returns the output of all** TRUE** arguments.

`👉`

Lastly, **IF **function shows** “Yes**” or “**No**” according to the output from **AND** function.

- Lastly, drag the Fill Handle down to the last row to see the outcome.

## 📝 Takeaway from This Article

`📌`

This article demonstrated 8 formulas to return value if a cell contains text in Excel.

`📌`

In the first method, we used **IF **function for returning value if the cell contains text in Excel.

`📌`

Then, we applied **VLOOKUP** function to return a value if a cell contains a text in Excel.

`📌`

After that, we demonstrated **INDEX **and **MATCH **functions for returning value if the cells contain text in Excel.

`📌`

Next, we employed **IF** and **COUNTIF** functions to return a value if the cell contains text in Excel.

`📌`

Then, we applied **ISNUMBER **and **SEARCH** functions for returning value if the cell contains text in Excel.

`📌`

After that, we demonstrated **IF **and **EXACT **functions to return value if cells contain text in Excel.

`📌`

Next, we employed **IF**, **OR**, **ISNUMBER, **and **SEARCH** functions for returning values if cells contain text in Excel.

`📌`

Finally, we combined **IF**, **AND**, **ISNUMBER **and **SEARCH **functions to return the value if the cell contains text in Excel.

## Conclusion

This article has demonstrated 8 formulas to return value if a cell contains text in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit **Excelden.com**.

## Related Articles

**If Cell Contains Text From List Then Return Value in Excel****Excel Formula: If Cell Contains Text Then Return Value in Another Cell****If One Cell Equals Another Then Return Another Cell in Excel**