Searching keywords from a list of words?

Hi, I have two sheets, say Sheet 1 and Sheet 2.

Sheet 2 contains a list of words in one column (i.e. "Keyword").

Sheet 1 contains a column with status updates (i.e. "Comments").

I would like to set up a formula that searches if Sheet 1's "Comments" column has any matching keywords from the list of words hosted in Sheet 2's "Keyword" column.

Is there any way to do that?

Find more posts tagged with

Formulas

Automations

Workflows in Smartsheet

Cell linking

References

Accepted answers

All comments

BraylenStanton

You need to create a formula that will look for every comment in Sheet 1 in the list of keywords on Sheet 2. For example, if the list of keywords is in Column A on Sheet 2 and the comments are in Column B on Sheet 1, you could use a formula like : =IF(ISNUMBER(SEARCH(A2, 'Sheet 1'!B:B)), "There is a match", "There is no match"). This formula will scan each comment in Sheet 1 and check if it contains any of the keywords from Sheet 2. If so, it will return "There is a match", otherwise it will return "No match". Be sure to include correct cell references and sheet names in the formula.

yh374

https://community.smartsheet.com/discussion/comment/428028#Comment_428028

So I tried the formula above along with ISTEXT but got #UNPARSEABLE error. How my sample sheet looks like (I decided to put the list of keywords column in the same sheet to make it easier):

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.