• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match multiple text strings in a single cell against list in separate worksheet

nic812

New Member
I have a column of data in which each of the cells can contain multiple product names separated by commas (call this column "Services"). The cells in the Services column can have 1 product name or up to 15 product names (again, separated by commas if there are multiple text strings).

I then have a named range (call it "PRODUCT") stored on another worksheet in the same workbook. I need to search all of the text in the cells in the Services column to see if anything there matches anything in the PRODUCT named ranged. If yes, have it return a "Yes."

EXAMPLE
In Cell A1 (Services column) it contains: "Enterprise Planning, Protection Service, Training"
In the other worksheet, the PRODUCT named range contains "Training" in the list
In Cell B1, show Yes" because one of the text strings in cell A1 matches one of the items in the named range.

How do I write this formula? To reiterate: the goal is to look at one cell filled with multiple strings of text, ask "does anything in that cell contain any of the keywords contained in named ranged on the other worksheet? If the answer is "Yes" return yes, and if the answer is no, return no (or blank).
 
Hi, nic812!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.

Regards!
 
Hi ,

Can you clarify whether there will be strings in the PRODUCT named range , which will also be sub-strings ?

For instance , to take the same example that you have mentioned , the PRODUCT named range may have as its entries :

Enterprise Planning

Protection Service

Training

Planning

Service

Is the above possible ?

Narayan
 
Here is a sample file that I mocked up to give you an idea of what I am trying to accomplish.
 

Attachments

  • Sample.xlsx
    9.4 KB · Views: 6
Hello Narayan. Thank you for putting the formula into the sample data file. I have done some spot verification on my data and all appears to be working. THANK YOU.

Formula in the cells =IF(OR(ISNUMBER(SEARCH(PRODUCTS,L2))),"Yes","No")

Sheet2 PRODUCTS range has =Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A))
 
Back
Top