# How to Return Value If Cells Contain Certain Text from a List

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.

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.

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.

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.

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.

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.

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.

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.

## 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)}

### 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)

### 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.]

### 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.

## 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.

## Related Articles

#### Chinmoy Mondol

Greetings! Thank you for visiting my profile. I am Chinmoy Mondol. I am a conscientious, tech enthusiast individual with a voracious appetite for knowledge and a desire to learn more. I graduated from American International University-Bangladesh with a Bachelor's Degree in Computer Science and Engineering. I enjoy using my skills to contribute to the exciting technological advances that happen every day. Constant advancement and personal development are my guiding principles.

1 Comment
1. 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 ?