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

yits05

New Member
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.

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JoeMo

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

Code:
``=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

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

Code:
``=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

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

VBA Code:
``````=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:
VBA Code:
``=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," ")``

Last edited:

yits05

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

VBA Code:
``````=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:
VBA Code:
``=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

Well-known Member
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:

Replies
2
Views
169
Replies
4
Views
146
Replies
16
Views
772
Replies
4
Views
108
Replies
1
Views
41