If cell contains one word from List A AND one word from list C, return adjacent cell

[yits05]

So far I have this formula which does a great job of searching the column 'main column'!O2 for words in 'Keywords $A$1:$A$12' and returning the adjacent cell.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2)))>0,'main column'!Q2," ")

However, I have another list, in Keywords2!$A$1:$A$12, and I would like the formula to only return the adjacent cell (main column'!Q2) if 'main column'!O2 contains a word from 'Keywords $A$1:$A$12' AND Keywords2!$A$1:$A$12


Thank you.

 

[JoeMo]

Untested, but you need to add the AND condition by using the multiplication operator (*) something like this:

=IF(SUMPRODUCT((--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2))>0)*(--ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2))>0)),'main column'!Q2,"")

 

[yits05]

Untested, but you need to add the AND condition by using the multiplication operator (*) something like this:

=IF(SUMPRODUCT((--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2))>0)*(--ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2))>0)),'main column'!Q2,"")

Thank you, but this seems to return only N/As, even when I know it should be returning proper results. Appreciate any additional help.

 

[DanteAmor]

Try this, In this way we ensure that there is at least one word in Keywords and in Keywords2.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2)))>0,
IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2)))>0,'main column'!Q2," ")," ")

Edit:

Reviewing Joe's formula, it should look like this:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2)) * --ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2)) )>0,'main column'!Q2," ")

 

[yits05]

Try this, In this way we ensure that there is at least one word in Keywords and in Keywords2.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2)))>0,
IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2)))>0,'main column'!Q2," ")," ")

Edit:

Reviewing Joe's formula, it should look like this:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("*"&Keywords!$A$1:$A$12&"*",'main column'!O2)) * --ISNUMBER(SEARCH("*"&Keywords2!$A$1:$A$12&"*",'main column'!O2)) )>0,'main column'!Q2," ")

Thank you, but I still only get N/As all the way down...

 

[DanteAmor]

You can put a sample of the three sheets:
main column
Keywords
Keywords2

What is the name of the sheet where the formula is?

To put examples you can use the XL2BB tool:

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com