如果单元格列在另一列中部分包含文本 [英] If Cell In Column Partially contains text in another Column

问题描述

我一直在查看所有不同的来源，找不到确切的答案。希望有人能帮助我。

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

我有两列：

COL1     COL2
abc      defghe
def      iabclmn
ghi      zhued
fgh      lmnop

我想知道COL2中是否存在一个值。所以在这种情况下，我希望它像这样：

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

是否有功能那可以做到这一点，我有超过500行，所以我不能只提出具体的值？

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

谢谢！

推荐答案

这样做：

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

SUMPRODUCT（）强制它遍历列B并跟踪返回true的那些。所以如果有的话，它会添加到池中。

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.

> 0 测试是否返回TRUE。

The >0 test whether any returned TRUE.

这篇关于如果单元格列在另一列中部分包含文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！