问题描述
我有一个职位列表 (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:找出单元格是否包含/包含一系列单元格中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！