excel - If Cell In Column Partially contains text in another Column

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