Searching keywords from a list of words?

yh374
yh374 āœ­āœ­āœ­

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?

Answers

  • BraylenStanton
    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
    yh374 āœ­āœ­āœ­
    edited 04/01/24

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!