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

yits05

Board Regular
Joined
Jul 17, 2020
Messages
56
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,907
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

Board Regular
Joined
Jul 17, 2020
Messages
56
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
14,792
Office Version
  1. 2010
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

Board Regular
Joined
Jul 17, 2020
Messages
56
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
14,792
Office Version
  1. 2010
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,113
Messages
5,835,456
Members
430,357
Latest member
Greshapa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top