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屋!