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?
Answers
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!