Excel-单元格包含列表中的值-返回列表值 [英] Excel - Cell Contains a Value from a List - Return list value

问题描述

我想返回A列中包含的相应匹配关键字，但是我不知道要使用Excel查询.

I want to return the corresponding matched keyword that is contained in Column A, but I dont know the Excel query to be used.

请您能帮忙吗?

详细信息如下:

A列-我需要与关键字匹配的公司列表(C列)

Column A - List of Firms I need to match the Keywords Against (Column C)

B列-如果关键字列表与A列中的单元格匹配，请在此处返回匹配值

Column B - If the list of Keywords match the cell in Column A return the Matching value here

C列-将这些关键字与A列中的文本匹配，我在寻找包含匹配而不是完全匹配

Column C - Match these keywords to text in Column A, I am looking for a contains match rather than a Exact Match

这里是有问题的文件: https://www.dropbox.com/s/zwrqzb60vnadgu8/Test.xlsx?dl = 0

Here is the file in question: https://www.dropbox.com/s/zwrqzb60vnadgu8/Test.xlsx?dl=0

推荐答案

以下是在B2中输入并下拉的一些建议:-

Here are a couple of suggestions to be entered in B2 and pulled down:-

(1)区分大小写，必须作为数组公式输入，如果有多个匹配关键字，则给出最后匹配:-

(1) Case sensitive, must be entered as an array formula, gives last match if more than one matching keyword:-

=INDEX(C$2:C$5,MAX(ROW(C$2:C$5)*(1-ISERROR(FIND(C$2:C$5,A3))))-1)

(2)不区分大小写，一个普通公式，如果有多个匹配关键字，则给出第一个匹配项:-

(2) Not case sensitive, an ordinary formula, gives first match if more than one matching keyword:-

=INDEX($C$2:$C$5,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($C$2:$C$5,A2)),,),0))

请注意，"Affinity Investment Group"(匹配投资组)不匹配，因为关键字是"Investments"(复数).

Note that "Affinity Investment Group" won't match because the keyword is "Investments" (plural).

这是第二个添加了IFERROR测试的版本，因此在没有匹配项时显示为空单元格:-

This is the second version with an IFERROR test added so that it shows an empty cell when there is no match:-

这篇关于Excel-单元格包含列表中的值-返回列表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！