如何实现单元格区域与值列的部分匹配并提取对应结果

方案1：Excel公式实现 #

适用Excel 365/2021及以上版本 #

直接在RO工作表Q2单元格输入如下公式，下拉填充即可：

=XLOOKUP(TRUE,ISNUMBER(SEARCH(LookUp!E$2:E$[你的城镇数据最后一行行号],K2)),LookUp!E$2:E$[你的城镇数据最后一行行号],"")

• SEARCH函数默认不区分大小写，刚好匹配需求，功能是判断指定城镇名是否存在于当前K列的地址中，存在则返回位置，否则返回错误
• XLOOKUP会返回第一个匹配到的城镇名，没有匹配结果则返回空值
• 注意将公式中[你的城镇数据最后一行行号]替换为LookUp表E列城镇的实际最后一行行号，不要直接引用整列避免卡顿

适用2019及更低版本Excel #

在RO工作表Q2单元格输入如下公式，输入完成后按Ctrl+Shift+Enter以数组公式形式确认，之后下拉填充即可：

=IFERROR(INDEX(LookUp!E$2:E$[你的城镇数据最后一行行号],MATCH(TRUE,ISNUMBER(SEARCH(LookUp!E$2:E$[你的城镇数据最后一行行号],K2)),0)),"")

方案2：修正后的VBA代码实现 #

你之前的VBA存在两个核心问题：一是每一行地址仅匹配了LookUp表同一行的城镇，没有遍历全部城镇列表；二是Like判断没有加通配符，也没有做不区分大小写的设置。
修正后的代码如下，支持批量处理，数据量较大时效率更高：

Option Compare Text '全局设置不区分大小写
Sub ROITown()
    Dim ws As Worksheet, ls As Worksheet
    Dim lRowRO As Long, lRowLookUp As Long
    Dim arrTown As Variant
    Dim i As Long, j As Long
    ' 绑定工作表
    Set ws = Worksheets("RO")
    Set ls = Worksheets("LookUp")
    ' 获取两个表的有效数据行数
    lRowRO = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
    lRowLookUp = ls.Cells(ls.Rows.Count, "E").End(xlUp).Row
    ' 把城镇列表一次性读入数组，提升运行效率
    arrTown = ls.Range("E2:E" & lRowLookUp).Value
    ' 遍历RO表所有地址行
    For i = 2 To lRowRO
        ws.Cells(i, "Q").Value = "" ' 先清空原有内容
        ' 遍历所有城镇匹配
        For j = 1 To UBound(arrTown)
            If arrTown(j, 1) <> "" Then
                ' 通配符匹配，判断城镇是否存在于地址中
                If ws.Cells(i, "K").Value Like "*" & arrTown(j, 1) & "*" Then
                    ws.Cells(i, "Q").Value = arrTown(j, 1)
                    Exit For ' 匹配到就退出，不需要继续找
                End If
            End If
        Next j
    Next i
    ' 释放对象
    Set ws = Nothing
    Set ls = Nothing
End Sub

注意事项 #

• 如果存在包含关系的城镇名（比如「New York」和「York」），建议提前把LookUp表的城镇列表按名称长度从长到短排序，避免短名称先被匹配导致结果错误
• 如果需要返回所有匹配到的城镇名而不是第一个，365版本可以把XLOOKUP替换为TEXTJOIN+FILTER组合实现

内容的提问来源于stack exchange，提问作者Rkayy