Excel:找出单元格是否包含/包含一系列单元格中的值 [英] Excel: Find out if cell contains/includes value from a range of cells

问题描述

我有一个职位列表 (A) 和短语列表 (B).对于 A 中的每个标题，我想检查它是否包含来自 B 的短语(任何短语，我不在乎是哪个).

I have a list of job titles (A) and list of phrases (B). For each title in A I want to check if it contains a phrase (any phrase, I don't care which) from B.

| 1 |-----Example Column A------|----Example Column B----        
| 2 |    Head of Marketing      |    Senior Developer          
| 3 |  Lead Product Engineer    |    Marketing Manager       
| 4 |     Sales Development     |    Sales Development   
| 5 |  Senior Marketing Manager |      

在上面的例子中，我想知道 A 列的最后两个单元格包含 B 列的单元格.

In the above example, I want to know that the last two cells in column A contain cells in column B.

我在网上找到了很多关于如何进行反向操作的示例，使用 * 来查找某个值是否包含在某个范围内.我还发现以下三个示例可以解决与我类似的问题，但没有一个对我有用.

I found lots of examples online of how to do the reverse, using * to find if a value is contained in a range. I also found the following three examples offered as solutions for problems similar to mine, but none worked for me.

{=MAX(ISNUMBER(SEARCH($B$2:$B$4,A2))+0)}         
{=MATCH(A2,$B$2:$B$4&"*")}
=IFERROR(LOOKUP(2^15,SEARCH(B:B,A2),B:B),"")

我还尝试将 B 列中每个单元格的内容写入以星号开头和结尾(*高级开发人员 * 等)，尝试使用 VLOOKUP、SUMPRODUCT、COUNTIF 均未成功.

I've also tried writing the contents of each cell in column B to start and end with an asterisk (*Senior Developer *, etc.), trying VLOOKUP, SUMPRODUCT, COUNTIF without success.

可以做我想做的事吗?

解决方案

稍微修改了 Mrig 的公式:

Slightly modified Mrig's formula:

=SUMPRODUCT(ISNUMBER(FIND(B$2:B$4,LOWER(A2)))*1)

推荐答案

试试这个

=SUMPRODUCT(ISNUMBER(FIND($B$2:$B$4,$A2))*1)

此公式将返回匹配的短语数.

This formula will return the number of phrases matched.

如果你不想要计数，你可以把这个公式放在 IF 中.

You can put this formula in IF if you don't want the count.

=IF(SUMPRODUCT(ISNUMBER(FIND($B$2:$B$4,$A2))*1)>0,"Exist","Does Not Exist")

查看图片以供参考

按降序对 Column A 进行排序后，我得到的是:

After sorting Column A in descending order output I get is:

这篇关于Excel:找出单元格是否包含/包含一系列单元格中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！