=IF(SUMPRODUCT(--ISNUMBER(SEARCH( with multiple AND criteria?

[yits05]

Hi,

I was hoping to get some help with the following:

I am using this formula to search "main column" containing strings for keywords contained in "Keywords" column. If it finds one of the keywords, it returns an adjacent cell to the string.

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



HOWEVER, I would like it to search TWO keyword lists, and only return the adjacent cell if the string in "main column" contains one keyword from Keywords AND one keyword from another list called "Keywords2"



Much appreciated!!

 

[JoeMo]

THIS POST APPEARS TO BE IDENTICAL TO THE ONE IN THE LINK BELOW. PLEASE DO NOT POST DUPLICATES.

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

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

www.mrexcel.com

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).