How to Return Value in Excel If Cell Contains Text from List

Поділитися
Вставка
  • Опубліковано 29 жов 2024

КОМЕНТАРІ • 43

  • @galiat8919
    @galiat8919 8 місяців тому

    It takes me a long time to finally find the good video ! Thanks for this complete tutorial ! It helps me a lot !

  • @matricus
    @matricus Рік тому +3

    Index/Match exactly what I was looking for. Not what you used it for though, but similar, thanks.
    Generalized: =IFERROR(INDEX(ReturnRange,MATCH(1,COUNTIF(LookupValue,"*"&LookupRange&"*"),0)),"not found")

    • @exceldemy2006
      @exceldemy2006  Рік тому

      Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
      Regards,
      Exceldemy Team!

  • @markfantasia3018
    @markfantasia3018 7 місяців тому +2

    This is a great formula! Is there a way to return a value from a second column (like if there was a numeric code for the type in addition to the type name)?

    • @exceldemy2006
      @exceldemy2006  7 місяців тому

      Dear, thanks for the comment. Your appreciation means a lot to us.
      Thank you once again for sharing an exciting problem. Yes, there is a way to return values from multiple columns If a cell contains text from a target list.
      Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/Returning-values-from-multiple-columns-if-cell-contains-text-from-list.gif
      You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Mark-Fantasia-SOLVED.xlsx

  • @jonathantack4699
    @jonathantack4699 7 місяців тому +1

    thank you! implemented successfully!

    • @exceldemy2006
      @exceldemy2006  7 місяців тому

      Hello @jonathantack4699,
      You are most welcome. Please stay connected with us.
      Regards
      ExcelDemy

  • @ericlloyd3481
    @ericlloyd3481 5 місяців тому +1

    Exactly what I was looking for. Liked and subscribed

    • @exceldemy2006
      @exceldemy2006  5 місяців тому

      Hello @ericlloyd3481,
      Thanks a lot. Your appreciation means a lot to us. Please stay connected with us.
      Regards
      ExcelDemy

  • @chrisbooth8813
    @chrisbooth8813 11 годин тому

    What if the value that we want to return is located in a separate column next to the list? For example, lets say that column F has the value "Food" next to "Chips" and "Drink" next to "Cold Drinks" and I want to return the value from column F if the value from column E exists within the text strings from column B. I need this to be an array formula for Google sheets so I can't use the "OR" function or index/match.

  • @KatieDecker-he3sv
    @KatieDecker-he3sv 6 місяців тому +2

    this saved me hours of work! Thank you!!

    • @exceldemy2006
      @exceldemy2006  6 місяців тому

      Dear, You are most welcome. We're glad it helped.

  • @Bellaisabell7078
    @Bellaisabell7078 11 місяців тому +1

    Thanks for this tutorial. Is there a way for me to use the index match formula without the wildcard input? I am trying to return the exact word, not looking to see if words from my list are imbedded in other words. Example looking for “ate” but the formula returns a value if “crATE” is in the cell. Not having much luck trying to edit the formula on my own.

    • @exceldemy2006
      @exceldemy2006  11 місяців тому

      Dear @shantaeisabell7078,
      Thank you for your question. We appreciate your feedback. Regarding your question on whether it’s possible to use the index match formula without the wildcard characters to return an exact word. Yes, it is possible, although you need to use the XMATCH function which is similar to the MATCH function and offers added flexibility. You can find the solution to your query in the Excel file linked to this message.
      Excel file: www.exceldemy.com/wp-content/uploads/2023/11/Return-Exact-Word.xlsx
      Hopefully, this answers your query. Make sure to stay connected with Exceldemy! 💖 Have a good day.
      Regards,
      Exceldemy

  • @leonramirez1458
    @leonramirez1458 11 місяців тому

    This video helped me so much! Thank you!

  • @michaelcorona4144
    @michaelcorona4144 6 місяців тому +1

    YOU ARE A LIFESAVER!!!!!!! THANK YOU SO MUCH!!!!!

    • @exceldemy2006
      @exceldemy2006  6 місяців тому

      Hello @michaelcorona4144,
      You are most welcome. It's great to hear that our video solved your problem. Thanks for your appreciation. It means a lot to us. Please stay connected with us.
      Regards
      ExcelDemy

  • @flyingpug8686
    @flyingpug8686 8 місяців тому

    This is amazing, thank you so much! Question: is there a way to return multiple values? For example if one cell has both chips and cold drink, can it show them both?

    • @exceldemy2006
      @exceldemy2006  8 місяців тому +1

      Dear @flyingpug8686,
      Thank you for your question. You can return multiple values from a list with the help of this VBA macro. Enable the developer tab >> Insert the VBA code in a Module. You can follow these articles to enable the Developer tab and learn about VBA user-defined functions.
      Enable Developer tab: www.exceldemy.com/how-to-display-the-developer-tab-on-the-ribbon/
      User-Defined functions:
      www.exceldemy.com/vba-user-defined-function/
      *Insert this VBA code inside the Module:*
      Function ExtractItems(inputText As String, categories As Range) As String
      Dim category As Variant
      Dim outputText As String
      Dim item As Variant

      ' Initialize outputText as an empty string
      outputText = ""

      ' Loop through each category in the specified range
      For Each category In categories
      ' Check if the category is present in the inputText
      If InStr(1, inputText, category.Value, vbTextCompare) > 0 Then
      ' Loop through each item in the inputText
      For Each item In Split(inputText, ",")
      ' Check if the category is part of the item
      If InStr(1, item, category.Value, vbTextCompare) > 0 Then
      ' Concatenate the item to outputText
      outputText = outputText & Trim(item) & ", "
      End If
      Next item
      End If
      Next category

      ' Remove the trailing comma and space
      ExtractItems = Left(outputText, Len(outputText) - 2)
      End Function
      In the C5 cell, apply this function =IFERROR(ExtractItems(B5,$E$5:$E$6),"")
      “ExtractItems” is the VBA user-defined function. The first argument (B5 cell) contains the text values in the “All Products” column. The second argument (E5:E6) is the list and you can enter multiple values, just change the range and press the F4 key to lock the reference. The IFERROR handles any errors and returns blank in case an error occurs. Use the Fill Handle tool to copy the formula to the other cells.
      Make sure to stay connected with Exceldemy!❤🥳.
      Regards,
      Exceldemy

  • @gospelmoto2833
    @gospelmoto2833 11 місяців тому

    Great video! helpful. Thanks.

  • @jamisami2549
    @jamisami2549 6 місяців тому +1

    Hello thanks for this but can I ask more?
    Basically in your Index match function or text join function imagine the LIST IN column E were like this
    "Cold chips"
    "Hot chips"
    "Hot beverage"
    "Cold Beverage"
    How can we retrieve it based on this list?
    Of course please add products on column B with the above list. Would be highly appreciated if you can show that too.

    • @exceldemy2006
      @exceldemy2006  6 місяців тому

      Dear, Thanks for your comment. Absolutely, you can ask for more to learn more deeply.
      You have demonstrated the dataset based on your requirements, especially all the products and lists. To fulfil your goal, all you need to modify is the range that contains the list within the existing formula.
      Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/To-retrieve-products-based-on-this-list.gif
      You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/jami-sami-SOLVED.xlsx
      To retrieve products based on this list:
      1. Using INDEX And MATCH Function: =IFERROR(INDEX($E$5:$E$8, MATCH(1, COUNTIF(B5, "*"&$E$5:$E$8&"*"), 0)),"")
      2. Using TEXTJOIN Function: =TEXTJOIN(", ", TRUE, IF(COUNTIF(B5, "*"&$E$5:$E$8&"*"), $E$5:$E$8, ""))

    • @jamisami2549
      @jamisami2549 6 місяців тому +1

      @@exceldemy2006
      Thank you so much for this. Only issue is if you had
      "Hot"
      "Beverage"
      In the first 2 line of column E, would the right data be displayed? I tried, but unfortunately it showed me both, hot, hot beverage. It worked only when I had to put "Hot beverage" first then few rows down "hot"

    • @exceldemy2006
      @exceldemy2006  6 місяців тому

      @@jamisami2549 Dear, Thanks for explaining your problem further. The previous formulas check any match of characters in cell B5 with any part of the text in the list. So, when you have Hot and Beverage in the first two lines of the list, both will match because Hot is a part of Hot Beverage.
      To overcome your situation, you can try using the following formulas:
      1. Using INDEX And MATCH Function: =IFERROR(INDEX($E$5:$E$8, MATCH(1, COUNTIF(B5, "*"&$E$5:$E$8), 0)),"")
      2. Using TEXTJOIN Function: =TEXTJOIN(", ", TRUE, IF(COUNTIF(B5, "*"&$E$5:$E$8), $E$5:$E$8, ""))

  • @TejasGhate3999
    @TejasGhate3999 4 місяці тому +1

    What if the searched cell has multiple values? In the above example if one cell had both chips & cold drinks, how do you get both the results?

    • @exceldemy2006
      @exceldemy2006  4 місяці тому

      Hello Tejas,
      I also replied to your comment in the comment section of the article. Here I am attaching the reply again.
      If the search cell has multiple criteria, you can use the following formula:
      TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH($E$4:$E$5, B4)), $E$4:$E$5, ""))
      It checks if any of the values in the criteria range are found within the text in cell B4. The SEARCH function identifies the presence of these values, and ISNUMBER confirms their existence. The IF function returns the matching values, while TEXTJOIN concatenates them into a single string, separated by commas, ignoring empty results. This allows multiple matches to be displayed in one cell.
      Regards
      ExcelDemy

  • @linggymartinez5741
    @linggymartinez5741 9 місяців тому +2

    Hi. All those formulars gives me a #SPILL! error. Do you know why? Thanks

    • @exceldemy2006
      @exceldemy2006  9 місяців тому

      Dear @linggymartinez5741,
      Thank you for your feedback. Regarding your question on getting #SPILL! error. These are the possible solutions to #SPILL! error.
      Problem 1: As the formula returns multiple values, there may be empty strings or merged cells that are obstructing the range. If you click on the green triangle beside the #SPILL! error, Excel will display “Select Obstructing Cells” click on it to choose the cell that is responsible for the problem.
      Solution 1: Go to the Home tab >> Editing >> Clear All option before applying the formula.
      Problem 2: Excel Table does not support dynamic formulas. If you click the green triangle Excel will show “Spill range in table”.
      Solution 2: You have to convert the Table back to a normal range.
      Hopefully, this will help you solve the #SPILL! error. However, if the problem persists, you can post a description of the problem with your Excel file in our Exceldemy forum. You’ll find Excel experts to help you solve your problems, signup today and get free solutions.
      Exceldemy Forum: exceldemy.com/forum/
      Make sure to stay connected with ExcelDemy! 🎉❤. Have a good day.
      Regards,
      ExcelDemy

  • @mrwolfuk
    @mrwolfuk Рік тому

    Thank you, this is an amazing solution !

    • @exceldemy2006
      @exceldemy2006  Рік тому

      Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
      Regards,
      Exceldemy Team!

  • @ilikechocolate3741
    @ilikechocolate3741 2 місяці тому

    what if in my list of key words, ("Chips" and "Cold drinks" in this example), I have only "Chips", and the other is just a blank space? Assuming there could be blank cells in the column "all products"
    I tried doing this, but formula gives the false value all the time

    • @exceldemy2006
      @exceldemy2006  2 місяці тому

      Hello @ilikechocolate3741,
      To return the actual product names from "All Products" when they match the "Keyword List," and leave the cell blank if there is no match.
      You can try our TEXTJOIN formula, it gives the perfect result based on your conditions.
      =TEXTJOIN(", ", TRUE, IF(COUNTIF(B5, "*"&$E$5:$E$6&"*"), $E$5:$E$6, ""))
      Here we are giving you an alternate formula that checks for a match and returns the matched product name.
      =IF(AND(B5"", ISNUMBER(MATCH(B5, $E$5:$E$6, 0))), B5, "")
      Regards
      ExcelDemy

  • @jack2media
    @jack2media Рік тому +6

    None of them worked for me

    • @exceldemy2006
      @exceldemy2006  Рік тому

      Hello @jack2media,
      Thank you for your feedback. We’re sorry to hear that you’re experiencing issues with the methods shown in the video. Since all the methods combine multiple functions together, we suggest you copy and paste the formula from the practice workbook and then change the cell references and criteria according to your dataset.
      In case you’re still facing issues with the formulas you can reach us at the following email.
      Email: problem@exceldemy.com
      Regards,
      ExcelDemy

  • @anwarkrg9292
    @anwarkrg9292 4 місяці тому +1

    amazing, you are the life saver, thanks. subscribed!👍🙂

    • @exceldemy2006
      @exceldemy2006  4 місяці тому

      Dear, Thanks for your wonderful compliment! Welcome to the ExcelDemy community.