☰

比较两列的部分值并获取匹配项和列号的信息

tag-icon • tag-icon microsoft-excel worksheet-function
比较两列的部分值并获取匹配项和列号的信息

我使用了 VLOOKUP、MATCH、INDEX 甚至 Fuzzy 插件。我确信其中一种或几种组合可能有效，我只是不知道如何让它工作并一直得到“#N/A”...

问题：我有 2 列，总项目数不同，全是引用。其中一列的数字与另一列的数字部分相同，但有一些变化，如下所示：

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

但是我有数千个数字，我想将 A 中的每个单元格与 B 的整个范围进行匹配，如果有匹配，即使在单元格 B6544 中，我也想知道并得到一些内容（在 C 列中），例如“True B6544”。

我正在处理的一个（小）例子（不是确切的参考，那些是内部合理的数据）：

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

可以看出，J1 与 B1 部分匹配，因此 H1 返回“True, B1”。

如上所述，我尝试了 MATCH、VLOOKUP、INDEX 和 Fuzzy 插件。我知道我必须这样做：

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

但似乎没有什么作用...任何帮助都将不胜感激！

答案1

尝试这个小的用户定义函数：

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

在此处输入图片描述

如图所示，在K1进入：

=partialmatch($B$1:$B$10,J1)

并向下复制。

该例程从子字符串中剥离首位数字并尝试在列中找到它。

答案2

由于 Excel 不支持正则表达式，我认为没有不需要辅助列的公式。在我的示例中，

  • A 包含带有“-”的数字
  • B 包含匹配的数字
  • C 包含 B 的值，但不包含第一位数字
  • 如果 A 的值与 B 列中的任何值都不匹配，则 D 为“FALSE”，或者为“TRUE”+ 对匹配单元格的引用。

C1中的公式为：

=RIGHT(B1,LEN(B1)-1)

这将删除 B1 的第一位数字。

D1中的公式为：

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

请注意，虽然这应该会产生所需的输出，但我建议将 TRUE/FALSE 和单元格引用分成两列。因此，我将仅解释ADDRESS如果找到匹配项，它将为您提供单元格引用，否则将提供错误。

VLOOKUP在这里没有用，因为它将返回与匹配值位于同一行的值。MATCH另一方面，返回匹配值的行。

  • MID(A1,2,SEARCH("-",A1)-2)返回 A1 中的子字符串，从第二个字符开始，直到第一次出现“-”为止。这就是我们要查找的值 ( lookup_value)。
  • MATCH(lookup_value, C$1:C$10, 0)将返回 第一次出现的行lookup_value。由于我们无法将函数应用于范围，并且 Excel 不支持正则表达式，因此我们需要辅助列 C。最后一个参数 ( 0) 是必需的，因为值未排序。请注意，返回的行值是相对于指定范围的，因此如果您的范围不是从第 1 行开始，则必须考虑到这一点（例如，通过添加到ROW([first cell])-1的结果MATCH）。
  • ADDRESS(matched_row, COLUMN(B1))生成单元格引用。如果愿意，您可以使用绝对行号来代替COLUMN(B1)，但这样不太易于阅读。

您必须自己决定哪些引用应该是绝对的或相对的。

答案3

由于我们尚未掌握完整的情况，我仅尝试部分回答您的问题。

我添加了两个辅助列来处理数据并进行比较 - 这将使您更好地理解。

该C-helper列删除了0开头和结尾的- number。
该I- helper列删除了 的前导数字（或第一位数字）J col。然后，我对中的数据
进行了查找，并根据 vlookup() 结果找出是否找到了数据。 vlookupI-helperC - helper

我的公式

  • C - helper：==MID(B4,2,LEN(B4)-3)
  • I - helper：=MID(D4,2,LEN(D4)-1)
  • vlookup col： =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

问题的下一部分是找到此条目的位置。如果找到该条目 - 则返回其地址，否则返回字符串Not_Available is returned。示​​例数据现在如下所示

ABC - 助手 JI - 助手 vlookup col HI X-助手
找到 1 025983553-1 25983553 225983553 25983553 正确，B1 $C$4
2 025973223-1 25973223 222222345 22222345 未找到 FALSE 不可用
3 025965463-2 25965463 233444667 33444667 未找到 FALSE 不可用
4 025911122-4 25911122 211198989 11198989 未找到 FALSE 不可用
5 025998764-1 25998764 212989238 12989238 未找到 FALSE 不可用
6 025925925-3 25925925 224397501 24397501 未找到 FALSE 不可用
7 025900000-2 25900000 225973223 25973223 找到 True, B2 $C$5
8 025999999-5 25999999 223334445 23334445 not_found FALSE 不可用
9 025965453-6 25965453 211100110 11100110 未找到 FALSE 不可用
找到 10 025943536-2 25943536 225911122 25911122 正确，B4 $C$7

编辑

我的公式

  • X - helper：=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")

相关内容