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