In this lesson, I’ll demonstrate 5 effective approaches to find if a cell contains text from a particular list and then how to return value in Excel. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Learn to Return Value If Cell Contains Text from List in Excel Using These 5 Approaches

Here I will be presenting 5 different approaches to Return a Value if Cell Contains a Text From List. Carefully go through the **Steps** as well as **Breakdowns**. **Breakdowns **will help you to understand the function and also the formula we have written in each method. We have attached multiple screenshots regarding the solving procedure.

**Method 1**

### 1. Utilizing Combination of IF-OR and SEARCH Functions

Here a list of products is present with the type of the product itself. We are going to filter two types of products by the use of the formula stated in this approach. We are going to use the **IF**, **OR**, and **IS** Functions.

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

- First, select a cell in which you want to see the filtered data.
- Copy or type in carefully the following formula.

**=IF(OR(ISNUMBER(SEARCH($E$6,B6)),ISNUMBER(SEARCH($E$7,B6))),B6,””)**

**🔨 Formula Breakdown**

`👉`

**IF(OR(ISNUMBER(SEARCH($E$6,B6)),ISNUMBER(SEARCH($E$7,B6))),B6,””)**

The cell value of **B6 **was looked up using the **SEARCH** function. For “Cookies”, it has the output 11 -the starting position of the substring. For Cereal, it returns an error.

`👉`

**IF(OR(ISNUMBER(11),ISNUMBER(SEARCH(#VALUE))),B6,””)**

**ISNUMBER** function turned 11 and the error into a **TRUE** and a **FALSE** value respectively.

`👉`

**IF(OR(TRUE,FALSE)),B6,””)**

**OR **function delivers a boolean **TRUE** value if any of the given arguments are **TRUE**. Here, there is a **TRUE** argument. So it also delivers **TRUE** here.

`👉`

** IF(TRUE, “Newtons Fig Cookies – Cookies”,””)**

Because the conditions of **IF **is **TRUE **here, it delivers “Newtons Fig Cookies – Cookies” as the result.

`👉`

**Final Output**: Newtons Fig Cookies – Cookies.

- After hitting enter we will see this result.

- After using the fill tool for copying along the column look like the picture below.

We have successfully filtered only Cookies and Cereal from the list.

**📕 Read More: 8 Ways to Return Value If Cell Contains Text in Excel**

**Method 2**

### 2. Applying TEXTJOIN, COUNTIF Formula

This function will help us get our desired result. If you don’t know yet about the **TEXTJOIN** formula click the link to learn. Now let’s hop into our solution.

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

- First
**Select**the cell you want the result into and then simply type the following formula on the**formula bar**.

**=TEXTJOIN(“, “, TRUE, IF(COUNTIF(B6, “*”&$E$6:$E$7&”*”), $E$6:$E$7, “”))**

🔨 **Formula Breakdown**

`👉`

** TEXTJOIN(“, “,TRUE,IF(COUNTIF(B6,”*”&$E$6:$E$7&”*”),$E$6:$E$7,””))**

As shown above, the asterisk (*) serves as the wildcard character here which looked for “Cookies” and “Cereal” substring in Cell** B6 **which is “Newtons Fig Cookies – Cookies” string.

`👉`

** TEXTJOIN(“, “,TRUE,IF(COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*, *Cereal*),$E$5:$E$6,””))**

When a substring match occurred, **COUNTIF** function reported one. Since “Cookies” is discovered in Cell **B6**, it gives the array {1:0} as output.

`👉`

**TEXTJOIN(“, “,TRUE,IF({1;0},$E$6:$E$7,””))**

One value alone, “Cookies” was provided by **IF **function. This is because the first value of the argument (from the array) is one that is **TRUE **in boolean.

`👉`

**TEXTJOIN(“, “,TRUE,{“Cookies”;””})**

Due to the fact that just one of the values from the particular list was matched, the **TEXTJOIN** method was ineffective. If the values to compare were numerous, else it would supply each one with a comma (**,**) separator.

`👉`

**Final Output**: Cookies.

- Now hit
**Enter**and use the fill tool to get the desired result.

- Use the fill tool to copy along the column.

**Method 3**

### 3. Using EXACT Function with IF and TEXTJOIN Functions

In this section we will show you how to solve the “ If a cell contains text from the list then return value” problem by using **EXACT**, **IF**, and **TEXTJOIN** functions. Follow the instruction below,

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

- Select a cell and type in the following formula.

**=TEXTJOIN(“, “,TRUE,IF(EXACT(C6:C15,$E$6),B6:B15,””))**

**🔨 Formula Breakdown**

`👉`

**EXACT(C6:C15,$E$6)**

This section determines if the values in the range **C6:C15** match with Cell** E6** and yields **TRUE** or **FALSE**.

`👉`

**IF(EXACT(C6:C15,$E$6),B6:B15,””)**

This section provides us the names for which we receive **TRUE**.

`👉`

** TEXTJOIN(“, “,TRUE,IF(EXACT(C6:C15,$E$6),B6:B15,””))**

Now a comma is used after each name to complete the joining of all the names we asked for.

`👉`

**Final Output**: As we are looking for Cereal we get,“ Dr. Pepper, Nestle”.

- Now hit
**Enter**for the result. We can see we just filtered all the Cereal’s name from the list.

**Method 4**

### 4. Taking COUNTIF, IF & OR Functions to Return

Here we will be demonstrating **IF**, **OR** and **COUNTIF** functions to solve our problem. Let’s get started.

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

- At the beginning select a cell and copy the following formula carefully.

**=IF(OR(COUNTIF(B6,”*”&$E$6:$E$7&”*”)),B6,””)**

🔨 **Formula Breakdown**

`👉`

** IF(OR(COUNTIF(B6,”*”&$E$6:$E$7&”*”)),B6,””)**

The asterisk (*) in this case serves as a wildcard character.. It is searching for “Cookies” and “Chips”, the substring lying in Cell **B6** which is the “Newtons Fig Cookies – Cookies” string.

`👉`

**IF(OR(COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*, *Chips*)), B6, “”)**

For each substring match, the **COUNTIF** Function gave back one. As it found “Cookies” in Cell **B6**, it returned {1:0}.

`👉`

**IF(OR({1;0}), B6, “”)**

If any of the parameters are **TRUE**, the **OR** function takes a **TRUE** result. In this scenario, one (1)= TRUE.

`👉`

** IF(TRUE, “Newtons Fig Cookies – Cookies”, “”)**

Since the result of the** IF** function is **TRUE**, It gives back the intended result, the first argument.

`👉`

**Final Output**: Newtons Fig Cookies – Cookies.

** **

- Now hit
**Enter**.

- Next, fill in the rest of the column.

**Method 5**

### 5. Combining INDEX-MATCH with COUNTIF Function

Here we will be using the combination of **IFERROR**, **INDEX**, **MATCH**, and **COUNTIF** functions. An equivalent here to the TEXTJOIN formula is this. Follow the following instructions.

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

- To start with select a cell and type in the following formula.

**=IFERROR(INDEX($E$6:$E$7, MATCH(1, COUNTIF(B6, “*”&$E$6:$E$7&”*”), 0)),””)**

🔨 **Formula Breakdown**

** **`👉`

** IFERROR(INDEX($E$6:$E$7,MATCH(1,COUNTIF(B6,”*”&$E$6:$E$7&”*”),0)),””)**

In the formula, we have written up the Asterisk (*) which serves as the wildcard character. The character sought for “Cookies” and “Cereal” substring inside Cell **B6** which is the “Newtons Fig Cookies – Cookies” string.

`👉`

** IFERROR(INDEX($E$6:$E$7,MATCH(1,COUNTIF(“Newtons Fig Cookies – Cookies”,*Cookies*,*Cereal*),0)),””)**

When a substring match occurred, **COUNTIF **function reported one. As there is “Cookies” in Cell **B6**, it reports {1:0}.

`👉`

**IFERROR(INDEX($E$6:$E$7,MATCH(1,{1;0}),0)),””)**

The **MATCH** portion of the formula reported one because there was only one matching value, “Cookies”.

`👉`

** IFERROR(INDEX($E$6:$E$7,1),””)**

As it is in the List array, the **INDEX** Function returned “Cookies”.

`👉`

** IFERROR(“Cookies”,””)**

In light of this, we can state that **IFERROR** Function is employed to manage the errors that may arise in the absence of matches.

`👉`

**Final Output**: Cookies.

- Pressing enter, we can see the following result.

- Now, use the fill tool to apply the formula to all the cells.

**📕 Read More: Excel Formula: If Cell Contains Text Then Return Value in Another Cell**

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`

Use $ for absolute values.

## 📝 Takeaways from This Article

`📌`

Combining functions to write formulas.

`📌`

**COUNTIF**, **TEXTJOIN**,** SEARCH**, **INDEX**, **MATCH**, **IF**, **OR**, **EXACT**, **IS** function and how they work.

`📌`

Breakdown of every formula used here to understand properly.

`📌`

Different ways of returning value if a cell contains text from list.

**Conclusion**

I hope you were able to use the techniques I demonstrated above in this Excel lesson if a cell contains text from list and then returns a value. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own and take a look at the “If the cell contains text from the list then return value” Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. Again if you have any problem regarding Excel spit it in the comment box. The **Excelden **crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com**.

## Related Articles

**VLOOKUP If Cell Contains a Word Within Text in Excel****If One Cell Equals Another Then Return Another Cell in Excel**