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

yits05

New Member
Joined
Jul 17, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
May 26, 2009
Messages
17,325
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jul 17, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Jul 17, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,166
Members
410,775
Latest member
alal1030
Top