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

#### yits05

##### Board Regular
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

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

#### 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

##### Board Regular
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

##### Board Regular
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:

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.

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.

### Which adblocker are you using?

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

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