arrays - excel:如何识别包含从关键字列表中获取的文本关键字的行

Question

我有一列数据(称为 A)，其中每个单元格包含一长串单词，例如:

COLUMN A 
HORNBACH BAUMARKT ETOY, ETOY 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG 
MIGROS M EPALINGES, EPALINGES 
HORNBACH BAUMARKT ETOY, ETOY 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

我在另一列中还有另一个关键字列表(称为 B)，例如

COLUMN B 
MSFT 
Amazon 
Hornbach 
Jumbo 
OBI 
Lipo 
Ikea 
Coop 
Migros 
Casino

这就是我想做的:

对于 B 列中的每个关键字 K 检查 A 列中的每个单元格以查看该条目是否作为子字符串存在 如果是，则在 C 列的相邻单元格中输入关键字 K 如果不是，则保持 C 列中的相邻单元格不变重复下一个关键字 K

结果应该是:

COLUMN A ----------------------------------> COLUMN C 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
ALIGRO, CHAVANNES-PR 
DIPL. ING. FUST AG,ETO, ETOY 
AGIP SUISSE SA 224, LAUSANNE 
AMAZON MEDIA EU, LUXEMBOURG ---------------> Amazon 
MIGROS M EPALINGES, EPALINGES -------------> Migros 
HORNBACH BAUMARKT ETOY, ETOY --------------> Hornbach 
MANOR AG - 390, BASEL 
MANOR AG - 390, BASEL 
GLOBUS LAUSANNE, LAUSANNE

我可以看到如何使用 VBA 类型结构来执行此操作...但肯定有一种方法可以使用内置的 Excel 函数 - INDEX、HLOOKUP、SEARCH... 等来执行此操作。

我尝试过，但没有成功。如果有人有更好的主意，请告诉我。

Answer 1

假设每行最多只有 1 个匹配单词，您可以在向下复制的 C1 中使用此公式

=IFERROR(LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10),"")

IFERROR 函数仅在 Excel 2007 或更高版本中可用，对于早期的 Excel 版本请尝试此修改

=LOOKUP("zzz",IF({1,0},"",LOOKUP(2^15,SEARCH(B$1:B$10,A1),B$1:B$10)))

如果您想在单独的单元格中获得多个匹配项，则可以在 C1 中使用此“数组公式”，使用 CTRL+SHIFT+ENTER 确认，并尽可能向下和横向复制需要(与最大可能匹配数相称)

=IFERROR(INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1 :$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")

如果 A1 包含列表中的 3 个单词，那么这些单词将填充到 C1、D1 和 E1 中，而 F1 等将保留为空白

根据评论修改:

公式中的第一个范围(INDEX 的第一个参数)定义了获取结果的范围，因此要将其更改为 Z 列只需更改该部分，即

=IFERROR(INDEX($Z$1:$Z$10,SMALL(IF(COUNTIF($A1,"*"&$B$1:$B$10&"*"),ROW($B$1 :$B$10)-ROW($B$1)+1),COLUMNS($C1:C1))),"")