Excel索引匹配-具有多个结果的部分字符串 [英] Excel Index Match - Partial strings with Multiple Results

问题描述

我正在尝试调整在在线示例电子表格中找到的这段代码，但是我不太了解它.

I'm trying to tweak this piece of code I found in a sample spreadsheet online but I can't quite get my head around it.

基本上，原始电子表格会根据用户定义的查找执行INDEX/MATCH，并在连接列表中整齐地列出匹配项.示例电子表格的输出如下所示:

The original spreadsheet basically does an INDEX/MATCH based on a user-defined lookup and lists the matches neatly in a concatenated list. The sample spreadsheet's output looks like this:

http://i.stack.imgur.com/DyahB.png - Excel输出样例(请注意第一场和第二场比赛之间没有间隙)

http://i.stack.imgur.com/DyahB.png - Sample Excel Output (Note how there are no gaps between the first and second matches)

底层算法是:

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

现在，我希望查找取而代之的是检索 PARTIAL 匹配项，此外，像这样水平生成输出:

Now, I want the lookup to instead retrieve PARTIAL matches, and in addition, generate the outputs horizontally like so:

http://i.stack.imgur.com/ShED0.png -根据部分匹配水平生成输出

http://i.stack.imgur.com/ShED0.png - Output is generated horizontally based on partial matches

我不确定该怎么做.似乎我会以某种方式尝试更改IF条件，以在部分比赛中返回true，但我无法解决.请帮忙！

I'm not sure how I would go about doing this. It seems like I would somehow try and change the IF condition to return true on partial matches but I can't get my head around it. Please help!

推荐答案

假设部分匹配"是指以中的值开头的文本，然后在N1中使用此公式

Assuming by "partial match" you mean text that starts with the value in L1 then use this formula in N1

=IFERROR(INDEX($I$2:$I$8,SMALL(IF(LEFT($H$2:$H$8,LEN($L$1))=$L$1,ROW($I$2:$I$8)-ROW($I$2)+1),COLUMNS($N1:N1))),"")

通过 CTRL + SHIFT + ENTER 确认 并复制

confirm with CTRL+SHIFT+ENTER and copy across

要在文本中随处进行匹配，您可以使用此版本

For a match anywhere in the text you can use this version

=IFERROR(INDEX($I$2:$I$8,SMALL(IF(ISNUMBER(SEARCH($L$1,$H$2:$H$8)),ROW($I$2:$I$8)-ROW($I$2)+1),COLUMNS($N1:N1))),"")

这两个公式都不区分大小写，尽管您可以通过将SEARCH更改为FIND

Neither formula is case-sensitive, although you can easily make the latter so by changing SEARCH to FIND

使用IFERROR功能意味着您无需重复进行错误处理-需要Excel 2007或更高版本

Use of IFERROR function means you don't need repetition for error handling - needs Excel 2007 or later version

这篇关于Excel索引匹配-具有多个结果的部分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！