Excel: Check IF Cell CONTAINS TEXT From LIST

This Excel video tutorial demonstrates how to check if a cell contains text from a list.

This video will be useful to you if you are asking the following question:
How do you check if text is in a list in Excel?

If you are performing the following tasks it will also be useful:
Check if cell contains text from list and return value
Search for multiple text strings in cell
Check if cell equals one of multiple values
Search a cell for a list of words
------------------------

[RobGoldstein]

This is great. What I really need is to see it say "Pan", "Knife" or "Chocolate" in the B column

[brawth]

Perfect. To the point with just the right cadence. No intro... Just spot on.

[andreigiura]

Beautiful and useful thank you very much. I would have a request if I may, do you know any way to output the found value instead of just "yes" and ""? that would be awesome if would share that with us.

[UchihaSasukeKrad]

Still don't get why doesn't work withour the "array" method, but did work for me in the end. Thanks! Voting thumbs up!

[lampsizgod]

Exactly what I was looking for. To the point and works perfectly!

[harshitdawar]

I was about to dislike man, but completed the whole video and found that shift and enter stuff! IT DID

THANKS, A LOT MAN!

[mrgrossartig]

Thanks for the video. Ive been searching for this info for ages!

[christran]

Instead of "Yes" or "" as the return value, is it possible to change the return value to the matching text? For example, if left column contains "Chocolate", then right column will return the value exactly as "Chocolate". I'm working on an inventory list and I'm hoping to automatically list the characteristics of items based on their text description. Any help is greatly appreciated! Lastly, thank you for taking the time to put together this easy-to-follow and concise tutorial!

[macutube]

Thanks Chester, did exactly what I wanted and nicely explained, esp why the formulas on Excel help sites were not working on O365. Really enjoyed the "oops moment" when you didn't lock the cells before coping down - we've all been there! I didn't know the F4 trick so 2 lessons in 1
Again, thanks

[panagisfoteinopoulos]

Thank you for the very good video!

A question please: What do I have to do in order to get the line+row of the cell of the List of column D that was found in each cell of column A, instead of the "YES" answer

[bramire]

Thank you so so much. This was exactly what I needed to modify for a compare I need to run for our team

[bindureddy]

Thanks a lot. This helped me a lot by simplifying my work!

[tonyrando]

Fantastic and saved the day. Thank you!

[Freeman]

It worked for me for ctrl shift enter, I have 2016 version. Thanks very much!

[lucianj]

You probably saved my life tonight haha, I was pulling my hair out over an assignment and you had the answer. Thanks!!!

[PeterHansOfficial]

Amazing. Helped me at my job today with a complex project!

[toughman]

omg this is totally what i looking for the whole time, thank you sir

[eddanger]

FANTASTIC VIDEO!! I've looked everywhere for an easy way to do this and now I've found it!
Thanks so very much!
One issue I ran into though... if a term in the List Column matches ANY part of a word in the Product Column... it identifies it. This is a big issue for me as I'm doing Keyword Research and I want to delete the terms that are in my "Negative Keywords" Column (your "List" Column) from the actual Keyword Research Column. For example... one of the words in my Negatives Column is "lack". When I used your method to identify the terms, it brought back a number of terms that didn't match ANY of the terms in my Negative Keyword Column... Those Keywords all contained the word "black", and it flagged them because the term "lack" was in my Negatives column. So basically it's matching literally ANY part of any word (not just complete words)... so because "lack" is part of "black"... it's flagging it.

So... I was curious if you know of any further magic that would alleviate this issue? Just curious my friend... I'll make due in the meantime, but if you wouldn't mind helping me with this issue I would be very appreciative!

Thanks again!! You're the BEST!!

[mkv]

This is really useful. This is great. What I really need is to see it say "Pan", "Knife" or "Chocolate" in the B column. If it is yes.
please assist

[alesAk]

At the very end, how to return the same words from that list? So it will looks like extraction of a certain text from the same list it looking at.