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?


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