Excel/LibreOffice Calc反向部分匹配 [英] Excel / LibreOffice Calc Partial Match in Reverse

问题描述

我想使用一个查询表根据其描述列为每一行选择一个标签.描述包含映射到标签的关键字.因此，我需要与关键字列表进行部分匹配，如下所示:

I would like to use a lookup table to choose a tag for each row according to its description column. Descriptions contain keywords that map to tags. Therefore, I need to partially match against the keyword list as below:

  A                     B          C        D        E
1 Description           Tag                 Keyword  Tag
2 lorem KEYA ipsum                          KEYA     Tag A
3 dolor sit KEYC amet                       KEYB     Tag B
4 KEYB consectetur                          KEYC     Tag C
5 adipiscing elit KEYA                      KEYD     Tag D
6 sed do KEYB eiusmod                       

我想用D2:E5中的查询表中的值填充B列中的单元格.我可以使用类似的东西

I would like to fill cells in column B with values from the lookup table in D2:E5. I could use something like

= VLOOKUP(."& $ A2&."，$ D $ 3:$ E $ 6，2，0)

=VLOOKUP("."&$A2&".", $D$3:$E$6, 2, 0)

但是它不起作用，因为我尝试将全文与部分文本进行匹配.

but it won't work because I try to match full text against partial texts.

= VLOOKUP($ A2，."& $ D $ 3:$ E $ 6&."，2，0)

也不起作用.显然，仅在搜索条件中支持正则表达式.而且我不喜欢写类似的东西

does not work either. Apparently regular expressions are only supported in the search criterion. And I don't like to write something like

=IF(ISNUMBER(SEARCH("KEYA",$A2)), "Tag A", 
 IF(ISNUMBER(SEARCH("KEYB",$A2)), "Tag B", 
 IF(ISNUMBER(SEARCH("KEYC",$A2)), "Tag C", 
 IF(ISNUMBER(SEARCH("KEYD",$A2)), "Tag D", 
 ""))))

您有什么建议吗?

推荐答案

解析A列中的KEY x 文本，以用作VLOOKUP中的 lookup_value .

Parse the KEYx text from column A to use as the lookup_value in your VLOOKUP.

B2中的公式为=IFERROR(VLOOKUP(MID(A2,FIND("KEY",A2),4),$C$2:$D$5,2,FALSE),"").

附录:对于OpenOffice/LibreOffice:

Addendum: For OpenOffice/LibreOffice:

B2的公式为=IF(ISERROR(VLOOKUP(MID(A2;FIND("KEY";A2);4);$C$2:$D$5;2;0));"";VLOOKUP(MID(A2;FIND("KEY";A2);4);$C$2:$D$5;2;0)).根据需要填写.

The formula for B2 would be =IF(ISERROR(VLOOKUP(MID(A2;FIND("KEY";A2);4);$C$2:$D$5;2;0));"";VLOOKUP(MID(A2;FIND("KEY";A2);4);$C$2:$D$5;2;0)). Fill down as necessary.

这篇关于Excel/LibreOffice Calc反向部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！