Search for text in cell from multiple lists based on list title

[Retr0]

My title is probably a bit confusing and I need to explain it more. I am currently trying to find if a list of keywords appear at all in a cell's text. For example i want to know if the keywords apple and toothbrush ever appear in the query "I have apples for breakfast everyday." but look at each value in the list individually. Now I've already solved this part with a =SUMPRODUCT(--ISNUMBER(SEARCH(B$1:B$4,A1)))>0 formula. However, it needs to go a step further and I'm having trouble getting anything to work and am even wondering if this is possible.

So the issue is that each cell that I want to look up specific keywords in belongs to a specific group that has it's own list. For example the query "I have apples for breakfast everyday" would belong to the "Apple" group. The "Apple" group then has it's own list of keywords I need to look up within that cell like the term "apple" and "toothbrush." I also have another group called "Strawberry" with it's own set of keywords that I want to look up like "trees" and "grass." A visual aid is below.

Excel.PNG

To reiterate I need to be able to match the criteria of column C to column A. Then if those match then I need it to look up all the values in column D to look at a single cell in column B. So for example if the query group name Apple matches with the keyword group name Apple then it will search the cell B1 for all the keywords in column D that are in the Apple group name.

Please let me know if this is possible, if so, how or if you have any questions.

Thanks

[Limor_OP]

Something like that? See in E2 and down:

=IF(ISNUMBER(SEARCH(D2,VLOOKUP(C2,A:B,2,0),1)),D2,"None")

[Retr0]

Yes! Thank you so much!

[Retr0]

Just for my own curiosity could this work with essentially two criterias to match? Like what if each Group belonged to a Class, if you will. And that class had its own set of keywords that applied to all of the groups within it's class. See below image
Attachment 680483

Is it possible to match class with class? And then class and group name? Class keywords would apply to all groups within that class, but still looking at each group individually if that makes sense.

[Limor_OP]

Hi,
Please upload an excel sample file. You have instructions at the top of the page explaining how to do that.

[Retr0]

Here is an excel doc

[Retr0]

Also I'm realizing this brings back a result if it is anywhere in the vlookup. However, I need to know if each individual cell in column B has a result.

[bebo021999]

Assuming:
Take into account these cases:
Query vs Keyword
apple <> apples
breakfast <> break
Try:

Please Login or Register  to view this content.

with TRUE if keyword found.

[Retr0]

Appreciate the response and help, but it's not quite what I'm looking for. This formula is very close to what I need. However, instead of the formula returning if anything in column F is present in column C is it possible to get the formula to a result based on column C? I need to know if the keywords in column F exist in the individual cells in column C, then if the cell in column C contains one of the keywords in column F the formula would return that the cell in column C is TRUE it contains the keyword for example.

[Limor_OP]

You just want to return the word matched instead of TRUE/FALSE responses?

Try this in G2:
=IF(SUMPRODUCT(($A$2:$A$5=D2)*($B$2:$B$5=E2)*(LEN($C$2:$C$5)-LEN(SUBSTITUTE(" "&$C$2:$C$5&" "," "&F2&" ",""))>0))>0=TRUE,F2,"No match")