Excel: If Cell Contains ONE of MANY THINGS

This Excel video tutorial demonstrates how to check whether a cell contains one of many things or values. The solution uses the following functions:

SEARCH
ISNUMBER
SUMPRODUCT

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

[lennyc]

Thank you. I've been trying to solve this for a long time.

[donaldventrice]

This is one heck of a formula and it works great! It eliminates the need for several cumbersome formulas and I use it all the time. This is a great tool to have in my arsenal!

[JysieKemso]

Exactly what I was looking for :) thanks so much!

[mik]

Thank you, this will save time every month from now.

[memoperez]

This is a great tutorial Chester. Thanks for the tips. I used it in my multi-thousand line db and it works like a champ!!! Respect man!!!

[carmendianaberardesco]

Is there a way to populate Column B with the List names you have in column D? So instead of yes or blank, it will say Pan for those that have it, Kni for those that have it, and Wine for those that have it?

[thejackleader]

How do you bring back the value instead of 1 with out having to type in the value in the formula which would defeat the purpose?

[假千九爱好者]

Chester You make a very good video, I need to review it include the ads

[omershekhani]

If the first value in the "find text" range is not the first value in the "within text"), the formula is not working. Can someone please help

[imranfirozabadi]

Hi Chester,
How do I search multiple words like Apple, Mango, Berry, Orange in cell (A2) and if any match of those words are found than the result should show in B2?

[stevereed]

Rather than copy the formula down, could you specify the whole list (e.g. A:A)?

[iamaadil]

It dose'nt work until the sumproduct, Please be patient till the last step.

[amangautam]

is there any way i can search multiple words like rat, mango, toy etc. in whole excel have list of 25 words, which i want to search in whole excel file. i check if any of the 25 words in list are available in a particular excel sheet....pls help
is there any software which can search multiple words in one click

[naomib]

Is there a way to return which position it matched on instead of just "yes" ***without using nested if statements***? e.g., cell b2 returns 18 (where 'pan' starts), b5 returns 13 etc. [WITHOUT using nested IF statements, because the number of items in the list will vary -- 12-50 elements] I don't need to know _what_ it matched, just where any item begins within the text of the cells in column A