How to Return Value in Excel If Cell Contains Text from 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 five different formulas to perform this operation so that you can choose the perfect one for your situation, and return the value if the cell contains certain text from a list.


Download Practice Workbook

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


Introduction to Functions Used in This Article

The formulas that I used here use the following functions:

  • 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 1st range to evaluate.
  • criteria1 – The criterion to use on the 1st range.
  • range2 [optional]: The 2nd range, acts just like range1.
  • criteria2 [optional]: The criterion to use on the 2nd range. This function allows maximum of 127 ranges and criteria pairs.

  • 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: The separator between texts that the function going to combine.
  • ignore_empty: This argument specifies if the function ignores the empty cells or not.
  • text1: 1st text value (or range).
  • text2 [optional]: 2nd text value (or range).

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

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

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

  • 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: This argument specifies which text to find.
  • within_text: This specifies where to find the text.
  • start_num [optional]: With this, you will specify- from which position in the text string you will count the position of the specified text. Optional and defaults to 1 from left.

5 Formulas to Return Value in Excel If a Cell Contains Certain Text from a List

I will attempt 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 categories 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.


1. Combine COUNTIF, IF & OR Functions to Return Value If a Cell Contains a Text from a List

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.

Use the COUNTIF function to Return Value If Cell Contains Text from List

The formula is as follows:

=IF(OR(COUNTIF(B5,"*"&$E$5:$E$6&"*")),B5,"")

Formula Breakdown:

  • =IF(OR(COUNTIF(B5,"*"&$E$5:$E$6&"*")),B5,"")

Here, the Asterisk sign (*) is a wildcard character. It searched for “Chips” and “Cold Drinks” substring within Cell B5 which is "Ruffles - Chips" string.

  •  =IF(OR(COUNTIF("Ruffles - Chips",*Chips*, *Cold Drinks*)), B5, "")

The COUNTIF function returned one for every substring match. As "Chips" is found in Cell B5, it returns {1:0}.

  • =IF(OR({1;0}), B5, "")

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

  • =IF(TRUE, "Ruffles - Chips", "")

As the IF function’s value is TRUE, it returns the first argument which is the desired output.

Final Output: Ruffles – Chips

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(B5,"*"&$E$5:$E$6&"*")),TRUE,FALSE) 

Read More: If Cell Contain Word Then Assign Value in Excel (4 Formulas)


2. Use IF-OR Combination with SEARCH Function to Return Value with Multiple Conditions

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.

Use the SEARCH function to Return Value If Cell Contains Text from List

The formula is as follows:

=IF(OR(ISNUMBER(SEARCH($E$5,B5)),ISNUMBER(SEARCH($E$6,B5))),B5,"") 

Formula Breakdown:

  • =IF(OR(ISNUMBER(SEARCH($E$5,B5)),ISNUMBER(SEARCH($E$6,B5))),B5,"")

The SEARCH function searched the values of the List column in Cell B5. For “Chips” it returned 11 which is the starting position of the substring. For Cold Drinks, it returned an error.

  • =IF(OR(ISNUMBER(11),ISNUMBER(SEARCH(#VALUE))),B5,"")

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

  • =IF(OR(TRUE,FALSE)),B5,"")

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.

  • =IF(TRUE, "Ruffles - Chips","")

As the IF function’s value is TRUE, it returns the first argument which is the desired output.

Final Output: Ruffles – Chips

Note:

  • Here, I have shown the cell that matched but you can show any output you want by changing the IF functions output with your desired output.
=IF(OR(ISNUMBER(SEARCH($E$5,B5)),ISNUMBER(SEARCH($E$6,B5))),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.
  • For case-sensitive situations, we can use the below formula based on the FIND function instead of the SEARCH function.
=IF(OR(ISNUMBER(FIND($E$5,B5)),ISNUMBER(FIND($E$6,B5))),B5,"")

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


Similar Readings:


3. Use TEXTJOIN Formula to Return Value in Another Cell If a Cell Has a Text from a List

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 they matched with the Product and showed them to the Matched value from List column.

Use the TEXTJOIN function to Return Value If Cell Contains Text from List

The formula is as follows:

=TEXTJOIN(", ",TRUE,IF(COUNTIF(B5,"*"&$E$5:$E$6&"*"), $E$5:$E$6,"")) 

Formula Breakdown:

  • =TEXTJOIN(", ",TRUE,IF(COUNTIF(B5,"*"&$E$5:$E$6&"*"),$E$5:$E$6,""))

Here, the Asterisk sign (*) is a wildcard character. It searched for “Chips” and “Cold Drinks” substring within Cell B5 which is “Ruffles – Chips” string.

  • TEXTJOIN(", ",TRUE,IF(COUNTIF("Ruffles - Chips",*Chips*, *Cold Drinks*),$E$5:$E$6,""))

The COUNTIF function returned one for every substring match. As “Chips” is found in Cell B5, it returns {1:0}.

  • TEXTJOIN(", ",TRUE,IF({1;0},$E$5:$E$6,""))

The IF function returned only the “Chips” value as only the first value of its argument was one = True.

  • TEXTJOIN(", ",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.

Final Output: Chips

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


4. Use an INDEX MATCH Formula to Return Value If Cell Contains Specific Text

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 they matched with the Product and showed them to the Matched value from List column.

Apply the combination of MATCH & INDEX functions to Return Value If Cell Contains Text from List

The formula is as follows:

=IFERROR(INDEX($E$5:$E$6, MATCH(1, COUNTIF(B5, "*"&$E$5:$E$6&"*"), 0)),"") 

Formula Breakdown:

  • =IFERROR(INDEX($E$5:$E$6,MATCH(1,COUNTIF(B5,"*"&$E$5:$E$6&"*"),0)),"")

Here, the Asterisk sign (*) is a wildcard character. It searched for “Chips” and “Cold Drinks” substring within Cell B5 which is “Ruffles – Chips” string.

  • IFERROR(INDEX($E$5:$E$6,MATCH(1,COUNTIF("Ruffles - Chips",*Chips*,*Cold Drinks*),0)),"")

The COUNTIF function returned one for every substring match. As “Chips” is found in Cell B5, it returns {1:0}.

  • IFERROR(INDEX($E$5:$E$6,MATCH(1,{1;0}),0)),"")

The MATCH function returned one as there is only one value “Chips” that matched.

  • IFERROR(INDEX($E$5:$E$6,1),"")

The INDEX function returned “Chips” as it was the value in the List array.

  • IFERROR("Chips","")

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

Final Output: Chips

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


5. Apply EXACT Function with IF and TEXTJOIN

This is another solution to this problem in different situations. Here, I have fetched the cell value from the List column with a single member. We match this value with the Product and showed all the match values in a single cell.

Apply the Exact function to Return Value If Cell Contains Text from List

The formula is as follows:

=TEXTJOIN(", ",TRUE,IF(EXACT(C5:C14,$F$5),B5:B14,""))

Formula Breakdown:

  • EXACT(C5:C14,$F$5)

This part checks which values of the Range C5:14 match with Cell F5 and return TRUE and FALSE.

  • IF(EXACT(C5:C14,$F$5),B5:B14,"")

This part returns the names for which we get TRUE.

  • TEXTJOIN(", ",TRUE,IF(EXACT(C5:C14,$F$5),B5:B14,""))

Finally, this joins all the names with a comma after each name.


Quick Notes

All these formulas here (except the 2nd one) are array formulas. That means you have to press Ctrl+Shift+Enter instead of pressing just the Enter button for entering this formula. But if you are an Office 365 user, then you can apply them by pressing just Enter.


Conclusion

In this article, I’ve narrowed down different formulas for various cases to return value if a cell contains 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. Moreover, you can visit our blog for more such articles.


Related Articles

Chinmoy Mondol

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.

10 Comments
  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 ?

    • Dear Ashiliah
      Thank you for your comment.
      Let me now show you how you can solve your problem.
      Based on your description I’ve created the dataset.
      Here, you can see in the following picture that we have Chocolate lava Cake in cell A5. we keep this in Sheet1.

      After that, in Sheet2, we have Lava cake, Strawberry drink, and Banana muffin in cells A5, A6, and A7 respectively.

      Next, we will type the following formula in cell B5 of Sheet1.
      =TEXTJOIN(", ", TRUE, IF(COUNTIF(A5, ""&Sheet2!A5:A6&""),Sheet2!A5:A6, ""))

      After that, press ENTER.
      As a result, you can see Lava cake in column B of sheet1.

      I hope you understand the solution. If you have any problems you can always let us know in the comment section.

      Regard
      Afia Aziz kona

  2. I’m trying to use “3. Using the TEXTJOIN function” above in Excel 365, but it persistently returns #NAME? error on everything I try. Also tried Ctrl+Shift+Enter, even though not required in Excel 365

    My formula is:
    “=TEXTJOIN(“, “, TRUE, IF(COUNTIF($J$2,“*”&$F$2:$F$4&”*”), $F$2:$F$4, “”))”

    Problem appears to be with the “*,” because the cell array only works:
    “=TEXTJOIN(“, “, TRUE, IF(COUNTIF($J$2,“*”&{“NCM”;”BV”;”4+8″}&”*”), $F$2:$F$4, “”))”

    But when I include the “*” I get:
    “=TEXTJOIN(“, “, TRUE, IF(COUNTIF($J$2,{#NAME?;#NAME?;#NAME?}), $F$2:$F$4, “”))”

    Any idea what is causing the #NAME? result?

    • Dear W
      Thank you for your comment.
      Let me show you that your formula works properly.

      Here, I created the List column including cells F2:F4.
      Along with that, I created a Product column that includes cell J2.

      Here, as I do not know your actual dataset, I take List and Product according to my choices, however, the cells are the same as your description.

      Further, I type the following formula in cell B4.

      =TEXTJOIN(", ", TRUE, IF(COUNTIF(J2,"*"&$F$2:$F$4&"*"), $F$2:$F$4, ""))
      This is the same formula you mentioned in the comment.

      After that, I press ENTER.
      As a result, you can see the result in cell B4.
      Therefore, the formula works properly.

      I hope that your problem will be solved now.
      If you face any further problems, please share your Excel file with us in the comment section.

      Regards
      Afia Aziz Kona

  3. Hi,

    When I try to use the formula COUNTIF, the CRITERIA picks up only the first name in the list range and ignores rest of the list. Can you please guide me why?

    • I am having this issue as well, following the steps of it and criteria only picks the first cell. To make it clearer, in explanation it says the formula searches for “Chips” and “Cold Drinks” but when you apply the formula in excel it only searches for “Chips”. What am i missing i couldnt understand ?

      • Hi AHMETCAN,

        Thanks for your comment. I am replying to you on behalf of Exceldemy. To get all the desired values from the list, you need to drag the Fill Handle down to copy the formula. You can follow the steps below to get all the values.
        STEPS:
        1. Firstly, select Cell C7 and type the formula below:
        =IF(OR(COUNTIF(B7,"*"&$E$7:$E$8&"*")),B7,"")
        2. Press Enter to see the result.
        3. Thirdly, move the cursor to the bottom right corner of Cell C7, it will turn into a small black plus sign.
        4. Now, drag the Fill Handle down to Cell C16.

        5. Finally, you will see results like the picture below.

        I hope this will help you to solve your problems. Please let us know if you have other queries.
        Thanks!

  4. Hi SANKARSHAN
    Generally, you can set one CRITERIA when you apply only COUNTIF Function.
    However, if you use a formula that has multiple functions, you can set multiple criteria.
    Let me show you an example.
    ● Suppose you have a set of fruits. You want to count the instances when the fruit is either Mango or Apple.

    image-1

    ● Go to D5 and write down the following formula
    =COUNTIF(B4:B15,B4)+COUNTIF(B4:B15,B5)

    image-2

    Here, Excel will count the instances when the criteria is Mango or Apple from the same range B4:B15.
    ● Now, press ENTER to get the output.

    imgae-3

    I hope it helps.
    You can also check the following articles to internalize the concept.
    COUNTIF with Multiple Criteria in Different Columns in Excel
    COUNTIFS with Multiple Criteria (5 Easy Methods)

    Thank you.

  5. Hello AFIA, I’ve used exact example and used even the same cells and formula. But it still results in #Name error. What could be the reason?

    • Hello ANANDA,
      #NAME error mostly occurs when you misspell the function name. Please check if you have used the correct spelling of the function.
      There can be another reason behind this. If you notice you will see that W had fixed the Cell value in the COUNTIF function which is used as the range. Try to use only Cell J2 as the range without making it a fixed range.
      If you are using Excel 365 version you only need to press Enter after inserting this array formula. But, for previous versions press Ctrl+Shift+Enter.
      I hope that your problem will be solved now.
      If you face any further problems, please share your Excel file with us in the comment section.
      Regards
      Arin Islam

Leave a reply

ExcelDemy
Logo