Test if contains a string in a list

[books4cars]

Hi,

I have the following in D2 which contains "David & Rose Smith

=iferror(LEFT($C2,(FIND(" ",$C2,1)-1)),C2)

I want to test for strings listed in the named range, "NOGOOD".

If none of the string are in C2, I'd like to apply above. If any of the stings in NOGOOD appear in C2, the result should be blank.

I believe I'm simply having trouble nesting this correctly:



=if(SUMPRODUCT(COUNTIF(rng,"*"&substrings&"*"))>0,(LEFT($C2,(FIND(" ",$C2,1)-1)),C2),"")

Thanks

 

[Fluff]

How about
=IF(SUMPRODUCT(COUNTIF(C2,"*"&G2:G5&"*"))=0,IFERROR(LEFT($C2,(FIND(" ",$C2,1)-1)),C2),"")

Where the NoGood strings are in G2:G5

 

[books4cars]

Perfect! Thank you!

 

[Peter_SSs]

Perfect! Thank you!

Are you sure?

If C2 contains "David & Rose Williamson" and NOGOOD contains "Jones" and "Williams" then that formula returns blank even though none of the names in NOGOOD appear in C2.

If I am right in what you are trying to achieve, then try this test for the existence of the names. You can replace the Found/Not found with what you want. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

Excel Workbook
	C	D	E	F
1				Jones
2	David & Rose Williamson	Not found		Williams
Check List

#VALUE!

</td></tr></table></td></tr></table>