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.

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.

The dataset

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.

Entering the COUNTIF function formula

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

Changing the output with desired output

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.

Entering the SEARCH function formula

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

Changing the output with desired output

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.

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.

Entering the TEXTJOIN function formula

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.

Entering the INDEX and MATCH function formula

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.


Further Readings

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo