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

[@galiat8919]

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

[@matricus]

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]

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

[@michaelcorona4144]

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

[@exceldemy2006]

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

[@KatieDecker-he3sv]

this saved me hours of work! Thank you!!

[@exceldemy2006]

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

[@jonathantack4699]

thank you! implemented successfully!

[@exceldemy2006]

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

[@markfantasia3018]

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]

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

[@ericlloyd3481]

Exactly what I was looking for. Liked and subscribed

[@exceldemy2006]

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

[@anwarkrg9292]

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

[@exceldemy2006]

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

[@leonramirez1458]

This video helped me so much! Thank you!

[@gospelmoto2833]

Great video! helpful. Thanks.

[@flyingpug8686]

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]

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

[@Bellaisabell7078]

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]

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

[@jamisami2549]

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]

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]

@@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]

@@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, ""))

[@chrisbooth8813]

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.

[@exceldemy2006]

Hello @chrisbooth8813, You can use the FILTER function in Google Sheets to achieve this. Here’s a formula that should work for your case: =ARRAYFORMULA(FILTER(F:F, ISNUMBER(SEARCH(E:E, B:B)))) This formula checks if the values in column E exist within the text strings in column B and, if they do, returns the corresponding values from column F. The SEARCH function identifies if there's a match, while ISNUMBER converts the results to TRUE or FALSE, allowing FILTER to pull only the matched rows. Ensure the ranges (E, B, F) cover your entire dataset. Regards ExcelDemy

[@mrwolfuk]

Thank you, this is an amazing solution !

[@exceldemy2006]

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

[@TejasGhate3999]

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]

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]

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

[@exceldemy2006]

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

[@jack2media]

None of them worked for me

[@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: [e-mail] Regards, ExcelDemy

[@ilikechocolate3741]

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]

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

[@nathones]

how do i do this if i want to return a value in column G based on if the B contains a string found in F? I can't get it to work with V or X lookup

[@exceldemy2006]

Hello @nathones, To return a value from column G based on whether column B contains a string from column F, you can use the following formula: =IF(ISNUMBER(SEARCH(F1, B1)), G1, "") This checks if the value in column B contains the text from column F. If true, it returns the value in column G; otherwise, it returns an empty string. Adjust the row numbers based on your data. Let me know if you need more help! Regards ExcelDemy