If Cell In Column Partially contains text in anoth

Question

I have been looking through all different sources and cannot find the exact answer to this. Was hoping someone can help me out.

I have two columns:

COL1     COL2
abc      defghe
def      iabclmn
ghi      zhued
fgh      lmnop

I want to know if a value in COL1 exist in COL2. So in this case I want it to look like this:

COL1     COL2      COL3
abc      defghe     TRUE
def      iabclmn    TRUE
ghi      zhued      FALSE
fgh      lmnop      TRUE

Is there a function that can do this, I have over 500 rows so I cannot just call out specific values?

I know there is an example that does specific values like this, but I want it to be by the entire column:

=ISNUMBER(SEARCH(substring,text))

Thanks!

Answer 1

To do it for full columns as real non-array formula:

=COUNTIF(B:B,"*"&A1&"*")>0

Answer 2

This will do it:

=SUMPRODUCT(ISNUMBER(SEARCH(A1,$B$1:$B$4))*1)>0

The SUMPRODUCT() forces it to iterate through Column B and keep track of the ones that return true. So if any are found it adds 1 to the pool.

The >0 test whether any returned TRUE.