Checking whether a list of keywords is within multiple columns

[analyst0503]

Hello!

I need help with broadening a formula that I already have or finding a better way to recognize keywords within a large set of data.

Context:
I have a list of keywords that I need to search for within multiple columns. So far, I have been able to create a table which contains those keywords and then use a formula (
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Table3[[#All],[Column1]],F2))),"keyword","")) to return the word 'keyword' when the word is present in the cell or nothing for when there is nothing in that cell. I'm wondering if it would be possible to do two more things by either extending this formula or using a different method:

1. Would it be possible to search for these keywords within multiple columns, so that instead of just searching in F2 (as it would be in this formula), I can also search in G2, H2...
2. Is there a way for Excel to actually return the value it finds within the column? Note that the keywords should be searched for and returned as they are, e.g. I have both 'paper' and 'paper clip' as keywords.

Here is a screenshot of the keywords and what the data would look like (screenshots is all I can provide unfortunately):

Any help is greatly appreciated and please let me know if you need additional details.

Many thanks!

 

[DanteAmor]

Try this:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(Table3[List of keywords],F2:H2)),Table3[List of keywords],""))

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[amitinddr1]

Hi @DanteAmor
I have a similar situation. Can you suggest on this - Finding matching keywords from a range

Thanks
Amit