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
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!