确定列表A中的名称是否完美匹配，部分匹配或在列表B中找不到？ [英] Determine if name in list A has perfect match, partial match, or is not found in list B?

问题描述

在Excel中，我有两列属性名称。我需要确定列A中的名称是否完整匹配，部分匹配或第B列中没有匹配。

In Excel, I have two columns of property names. I need to determine if a name in Column A has a complete match, a partial match or no match in Column B.

除了设置这一点以外，令我感到困惑的是好吧，如果有多个局部比赛 - 我不认为使最佳匹配组件复杂化是合理的，但我不知道如何处理这一部分。

Something beyond setting this up that is confusing me as well is what if there are multiple partial matches - I don't think it is reasonable to complicate this with a "best match" component, but I'm not sure how to deal with this part.

如何做？

推荐答案

完全匹配

查找是否有完全匹配可以很容易 - 您可以使用 MATCH 函数：

Finding out if an exact match is available is easy - you can use the MATCHfunction for this:

=MATCH(B1,A:A,0)

将返回您找到B1的行号。将它与IFERROR组合以处理没有匹配的元素：

will return you the row number in which B1 is found. Combine it with IFERROR to handle elements that do not have any match:

=IFERROR(MATCH(B1,A:A,0),"No exact match")

或者，如果只有一个匹配但不在哪里，使用 ISERROR function：

Alternatively, if you're only interested if there is a match, but not where, use the ISERROR function:

=NOT(ISERROR(MATCH(B1,A:A,0)))

部分匹配

从您的评论我明白部分匹配是指列B中的列B中的完整字符串的出现。您可以使用 SEARCH 函数。但是，由于搜索只会检查一个单元格中的显示，您需要将其组合为数组公式：

From your comment I understand that a "partial match" means the occurrence of the full string in column B as a substring in column A. You can use the SEARCHfunction for that. However, as search will only check the appearing in one cell, you need to combine it as an array formula:

=MATCH(FALSE,ISERROR(SEARCH(B1,A1:A100)),0)

输入 Ctrl - Shift - 输入。

Enter it with Ctrl-Shift-Enter.

另请注意，出于性能原因，最好将范围限制为搜索，即代替 A：A ，使用 A1：A100 - 或任何行数。

Also note that for performance reasons it is better to limit the range to search, i.e. instead of A:A, use A1:A100 - or whatever your number of rows is.

这篇关于确定列表A中的名称是否完美匹配，部分匹配或在列表B中找不到？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！