If you have a list of text and want to search cells and return values based on the list, youâ€™ll need to build a formula because Excel doesnâ€™t provide a simple way for doing so. In this article, I have addressed this issue and provided four different formulas to perform this operation so that you can choose the perfect one for your situation, and return the value if cells contain certain text from a list.

**Table of Contents**hide

## Download the Workbook

You can download the workbook that I used in this article from below and practice with it by yourself.

I attempted to present a real-life example in this dataset. Some beverages are represented here. **Chips**, **Cold Drinks**, and **Cereals **are the three categories of beverages in this dataset. In a single column called **All Products**, the name and category of the beverages are linked together. Two of these categories, **Chips** and** Cold Drinks**, are also n the **List** column. Based on the **List** column, the desired output will be displayed in the second column.

## Breakdown of Functions Used in This Article

The formulas that I used here uses the following functions:

**1. The COUNTIFS function:**

This function counts cells that match multiple criteria. The syntax of the **COUNTIFS **function is as follows.

**=COUNTIFS (range1, criteria1, [range2], [criteria2], â€¦)****range1** â€“ The first range to evaluate.

**criteria1** â€“ The criteria to use on **range1**.

**range2 [optional]:** The second range to evaluate.

**criteria2** **[optional]: **The criteria to use on **range2**. Up to 127 **range/criteria pairs** are allowed.

You can learn about this function in detail by reading this documentation from Microsoft.

**2. The TEXTJOIN function:**

This function joins text values with a delimiter. The syntax of the **TEXTJOIN **function is as follows.

**=TEXTJOIN (delimiter, ignore_empty, text1, [text2], â€¦)****delimiter: **It is the separator between each text.

**ignore_empty: **Decides whether to ignore empty cells or not.

**text1: **First text value or range.

**text2 [optional]:** Second text value or range.

You can learn about this function in detail by reading this documentation from Microsoft.

**3. The MATCH function:**

This function gets the position of an item in an array. The syntax of the **MATCH **function is as follows.

**=MATCH (lookup_value, lookup_array, [match_type])****lookup_value: **The value to match in **lookup_array**.

**lookup_array: **A range of cells or an array reference.

**match_type [optional]:** 1 = exact or next smallest, 0 = exact match, -1 = exact or next largest. By default, match_type=1.

You can learn about this function in detail by reading this documentation from Microsoft.

**4. The INDEX function:**

This function gets values in a list or table based on location. The syntax of the **INDEX **function is as follows.

**=INDEX (array, row_num, [col_num], [area_num])****array: **Range of cells, or an array constant.

**row_num: **The row position in the reference.

**col_num [optional]**: The column position in the reference.

**area_num [optional]:** The range in reference that should be used.

You can learn about this function in detail by reading this documentation from Microsoft.

**5. The IFERROR function:**

This function traps and handles errors. The syntax of the **IFERROR **function is as follows.

**=IFERROR (value, value_if_error)****value:** The value, reference, or formula to check for an error.

**value_if_error: **The value to return if an error is found.

You can learn about this function in detail by reading this documentation from Microsoft.

**6. The SEARCH function:**

This function gets the location of text in a string. The syntax of the **SEARCH **function is as follows.

**=SEARCH (find_text, within_text, [start_num])****find_text**: The text to find.

**within_text:** The text to search within.

**start_num** **[optional]:** Starting position in the text to search. Optional, defaults to 1.

You can learn about this function in detail by reading this documentation from Microsoft.

## 4 Formulas to Return Value If Cells Contain Text from a List

### 1. Using the COUNTIF function

This is the most useful formula if you want to return the value of the whole cell after the match.

Here, I have fetched the cell values of the **Products **that matched the **List** column criteria and showed them to the **Product based on that list **column.

*Note: This formula is an array formula. You have to press Ctrl+Shift+Enter instead of pressing just the Enter button for entering this formula. Not applicable for Office 365 users.*

The formula is as follows:

**{=IF(OR(COUNTIF(B7,â€ť*â€ť&$E$7:$E$8&â€ť*â€ť)), B7, â€śâ€ť)}***Breakdown:*

*IF(OR(COUNTIF(B7,â€ť*â€ť&$E$7:$E$8&â€ť*â€ť)), B7, â€śâ€ť)*

*=> IF(OR(COUNTIF(â€ś*Ruffles â€“ Chipsâ€ť*,*Chips*, *Cold Drinks*)), B7, â€śâ€ť)*

*[Here, the Asterisk sign (*) is a wildcard character. It searched for â€śChipsâ€ť and â€śCold Drinksâ€ť substring within cell B7 which is â€śRuffles â€“ Chipsâ€ť string.]*

=> *IF(OR(*{1;0}*), B7, â€śâ€ť)*

*[The COUNTIF function returned one for every substring match. As â€śChipsâ€ť is found in cell B7, it returns {1:0}.]*

*=> IF(TRUE, â€śRuffles â€“ Chipsâ€ť, â€śâ€ť)*

[The OR function returns a TRUE value if any of the arguments are TRUE. In this case, one (1)= TRUE.]

=> *Ruffles â€“ Chips*

*[As the IF functionâ€™s value is TRUE, it returns the first argument which is the desired output.]*

**Note:** Here, I have shown the cell that matched but you can show any output as you want by changing the IF functions output with your desired output.

*{=IF(OR(COUNTIF(B7,â€ť*â€ť&$E$7:$E$8&â€ť*â€ť)),*

**TRUE, FALSE**)}**Read More:** **If Cell Contain Word Then Assign Value in Excel (4 Formulas)**

### 2. Using the SEARCH function

Here, I have fetched the cell values of the **Products **that matched the **List** column criteria and showed them to the **Product based on that list **column.

The formula is as follows:

**=IF(OR(ISNUMBER(SEARCH($E$7,B7)),ISNUMBER(SEARCH($E$8,B7))),B7,â€ťâ€ť)***Breakdown:*

*IF(OR(ISNUMBER(SEARCH($E$7,B7)),ISNUMBER(SEARCH($E$8,B7))),B7,â€ťâ€ť)*

*=>IF(OR(ISNUMBER(11),ISNUMBER(SEARCH(#VALUE))),B7,â€ťâ€ť)*

*[The SEARCH function searched the values of the List column in Cell B7. For â€śChipsâ€ť it returned 11 which is the starting potition of the substing. For Cold Drinks, it retunred error.]*

*=> IF(OR(TRUE,FALSE)),B7,â€ťâ€ť)*

*[The ISNUMBER function converted 11 into TRUE value and the error into FALSE value.]*

*=> IF(TRUE, â€śRuffles â€“ Chipsâ€ť,â€ťâ€ť)*

*[The OR function returns a TRUE value if any of the arguments are TRUE. As there is a TRUE argument, it also returns the TRUE value in this case.]*

=> *Ruffles â€“ Chips*

*[As the IF functionâ€™s value is TRUE, it returns the first argument which is the desired output.]*

**Note:** Here, I have shown the cell that matched but you can show any output as you want by changing the **IF **functions output with your desired output.

*{=IF(OR(COUNTIF(B7,â€ť*â€ť&$E$7:$E$8&â€ť*â€ť)),*

**1, 0**)}The main benefit of this formula is that this is not an array formula but it is not recommended if you have many cells in the **List **as you have to enter every cell of the **List** manually.

**Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)**

**Similar Readings:**

**How to Sum If Cell Contains Specific Text in Excel (6 Ways)****Use VLOOKUP If Cell Contains a Word within Text in Excel****How to find text in an Excel range & return cell reference (3 ways)**

### 3. Using the TEXTJOIN function

This formula is useful when you have to show what string or strings from the **List** matched.

Here, I have fetched the cell values from the **LIST** column where it matched with the **Product** and showed them to the **Matched value from List **column.

*Note: This formula is an array formula. You have to press Ctrl+Shift+Enter instead of pressing just the Enter button for entering this formula. Not applicable for Office 365 users.*

The formula is as follows:

**{=TEXTJOIN(â€ś, â€ś, TRUE, IF(COUNTIF(B7, â€ś*â€ť&$E$7:$E$8&â€ť*â€ť), $E$7:$E$8, â€śâ€ť))}***Breakdown:*

*TEXTJOIN(â€ś, â€ś, TRUE, IF(COUNTIF(B7, â€ś*â€ť&$E$7:$E$8&â€ť*â€ť), $E$7:$E$8, â€śâ€ť))*

=> *TEXTJOIN(â€ś, â€ś, TRUE, IF(COUNTIF(â€ś*Ruffles â€“ Chipsâ€ť*,*Chips*, *Cold Drinks*), $E$7:$E$8, â€śâ€ť))*

*[Here, the Asterisk sign (*) is a wildcard character. It searched for â€śChipsâ€ť and â€śCold Drinksâ€ť substring within cell B7 which is â€śRuffles â€“ Chipsâ€ť string.]*

=>*TEXTJOIN(â€ś, â€ś, TRUE, IF({1;0}, $E$7:$E$8, â€śâ€ť))*

*[The COUNTIF function returned one for every substring match. As â€śChipsâ€ť is found in cell B7, it returns {1:0}.]*

=>*TEXTJOIN(â€ś, â€ś, TRUE, {â€śChipsâ€ť;â€ťâ€ť})*

*[The IF function returned only the â€śChipsâ€ť value as only the first value of its argument was one = True.]*

*=> Chips*

*[The TEXTJOIN function didnâ€™t do anything here as only one value from the List was matched. If there were many values to match, it would have returned all of them with commas (,) between them as a separator.]*

**Read More:** **If Cell Contains Text Then Add Text in Another Cell in Excel**

### 4. Using the INDEX and MATCH function

This is an alternative to the **TEXTJOIN** formula. This formula also shows what string or strings from the **List** matched.

Here, I have fetched the cell values from the **LIST** column where it matched with the **Product** and showed them to the **Matched value from List **column.

*Note: This formula is an array formula. You have to press Ctrl+Shift+Enter instead of pressing just the Enter button for entering this formula. Not applicable for Office 365 users.*

The formula is as follows:

**{=IFERROR(INDEX($E$7:$E$8, MATCH(1, COUNTIF(B7, â€ś*â€ť&$E$7:$E$8&â€ť*â€ť), 0)),â€ťâ€ť)}***Breakdown:*

*IFERROR(INDEX($E$7:$E$8, MATCH(1, COUNTIF(B7, â€ś*â€ť&$E$7:$E$8&â€ť*â€ť), 0)),â€ťâ€ť)*

*=> IFERROR(INDEX($E$7:$E$8, MATCH(1, COUNTIF(â€śRuffles â€“ Chipsâ€ť,*Chips*, *Cold Drinks*), 0)),â€ťâ€ť)*

*[Here, the Asterisk sign (*) is a wildcard character. It searched for â€śChipsâ€ť and â€śCold Drinksâ€ť substring within cell B7 which is â€śRuffles â€“ Chipsâ€ť string.]*

*=>IFERROR(INDEX($E$7:$E$8, MATCH(1, {1;0}), 0)),â€ťâ€ť)*

*[The COUNTIF function returned one for every substring match. As â€śChipsâ€ť is found in cell B7, it returns {1:0}.]*

*=> IFERROR(INDEX($E$7:$E$8, 1),â€ťâ€ť)*

*[The MATCH function returned one as there is only one value â€śChipsâ€ť that matched.]*

*=> IFERROR(â€śChipsâ€ť,â€ťâ€ť)*

*[The INDEX function returned â€śChipsâ€ť as it was the value in the List array.]*

*=> Chips*

*[Here, the IFERROR function is used to handle the error that will occur if there are no matches.]*

**Note:** Here, I have shown the cell that matched but you can show any output as you want by changing the **IF **functions output with your desired output.

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

## Conclusion

In this article, Iâ€™ve narrowed down different formulas for various cases to return values if cells contain specific text from a list. I hope you were able to find a solution to your problem. Please leave a comment if you have any suggestions or questions. Thank you.

I have a question , I have a column with string of texts (sheet1) and I want to lookup to a list of texts (Sheet2) to find any matching words and return the matching words in sheet 1.

I.e sheet1

Column A

Chocolate lava cake

Sheet 2

Column A

Lava cake

Strawberry drink

Banana muffin

Result sheet 1

I.e

Column A

Chocolate lava cake

Column B

Lava cake

Would you be able to help me ?