How to Return Value in Excel If Cell Contains Text from List
HTML-код
- Опубликовано: 23 июл 2024
- In this video, I'll guide you through multiple methods to return value in Excel if a cell contains text from a list. You'll learn about using the COUNTIF Function, SEARCH Function, TEXTJOIN function, Combining INDEX and MATCH functions, and applying the EXACT function. By returning value in Excel, if a cell contains text from a list, you can categorize data, assign labels, or perform conditional calculations based on text values. With practical examples and step-by-step instructions, you can effortlessly return value if a cell contains text from a list in your Excel spreadsheets.
👨🏫 Instructor: Zehad Rian Jim
🎥 Editor: Sadia Rahman
▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬
0:00 - Intro
0:58 - Using COUNTIF Function
3:02 - Using SEARCH Function
4:30 - Using TEXTJOIN Function
6:24 - Combining INDEX and MATCH Functions
8:24 - Applying the EXACT Function
📚 ⯆ DOWNLOAD the workbook here:
www.exceldemy.com/if-cell-con...
🌍 ⯆ Checkout the article here:
www.exceldemy.com/if-cell-con...
🚩 Stay connected with us on social media for more Excel tips and tricks!
Facebook: / exceldemy
Twitter: / exceldemy
LinkedIn: / exceldemy
🙋♂️ Stuck with an Excel formula or a VBA code? You can post your questions or upload your Excel file to get in touch with the professionals and get the solution you need.
ExcelDemy Forum: exceldemy.com/forum/
👉 If you found this video helpful, don't forget to subscribe to our channel for more Excel tutorials, tips, and tricks! Hit the subscribe button and turn on notifications, so you never miss an upload. By subscribing, you'll be supporting our channel and helping us to reach more people who can benefit from our content. Thank you for watching, and we'll see you in the next video!
🔔 ⯆ Subscribe on RUclips:
/ @exceldemy2006
#excel #exceltutorial #exceltips #exceltricks
This video helped me so much! Thank you!
Great video! helpful. Thanks.
It takes me a long time to finally find the good video ! Thanks for this complete tutorial ! It helps me a lot !
this saved me hours of work! Thank you!!
Dear, You are most welcome. We're glad it helped.
Exactly what I was looking for. Liked and subscribed
Hello @ericlloyd3481,
Thanks a lot. Your appreciation means a lot to us. Please stay connected with us.
Regards
ExcelDemy
YOU ARE A LIFESAVER!!!!!!! THANK YOU SO MUCH!!!!!
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
thank you! implemented successfully!
Hello @jonathantack4699,
You are most welcome. Please stay connected with us.
Regards
ExcelDemy
amazing, you are the life saver, thanks. subscribed!👍🙂
Dear, Thanks for your wonderful compliment! Welcome to the ExcelDemy community.
Thank you, this is an amazing solution !
Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
Regards,
Exceldemy Team!
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")
Thank you! Stay connected with Exceldemy for more helpful content! 🎉❤
Regards,
Exceldemy Team!
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.
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
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?
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
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)?
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
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?
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
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.
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, ""))
@@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"
@@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, ""))
Hi. All those formulars gives me a #SPILL! error. Do you know why? Thanks
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
None of them worked for me
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