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

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:

🌍 ⯆ Checkout the article here:

🚩 Stay connected with us on social media for more Excel tips and tricks!

🙋‍♂️ 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.

👉 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 YouTube:

#excel #exceltutorial #exceltips #exceltricks

[markfantasia]

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

[galiat]

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

[flyingpug]

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?

[Bellaisabell]

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.

[jamisami]

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.

[KatieDecker-hesv]

this saved me hours of work! Thank you!!

[TejasGhate]

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?

[ericlloyd]

Exactly what I was looking for. Liked and subscribed

[linggymartinez]

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

[ilikechocolate]

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

[leonramirez]

This video helped me so much! Thank you!

[mrwolfuk]

Thank you, this is an amazing solution !

[anwarkrg]

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