如何用Excel公式实现多匹配CODE对应信息的拼接？

一、公式法（适用于Excel 365/2021） #

利用FILTERXML拆分描述文本，结合XLOOKUP匹配字段，最后用TEXTJOIN拼接结果，直接在Sheet2的新列输入公式即可：

1. 生成拼接后的CATEGORY列 #

假设Sheet2的DESCRIPTION在A列，在B2单元格输入：

=TEXTJOIN("|",TRUE,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),Sheet1!$A:$A,Sheet1!$B:$B,""))

下拉填充到所有行。

2. 生成拼接后的SUBCATEGORY列 #

在C2单元格输入：

=TEXTJOIN("|",TRUE,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),Sheet1!$A:$A,Sheet1!$C:$C,""))

下拉填充。

3. 生成拼接后的DETAILS列 #

在D2单元格输入：

=TEXTJOIN("|",TRUE,XLOOKUP(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s"),Sheet1!$A:$A,Sheet1!$D:$D,""))

下拉填充。

参数说明

• 把公式中的" "替换为DESCRIPTION中CODE的实际分隔符（比如逗号、分号）
• Sheet1!$A:$A是Sheet1的CODE列，Sheet1!$B:$B是CATEGORY列，按需替换为对应列
• TEXTJOIN的第二个参数TRUE会忽略匹配不到CODE的空值，若要保留空值可改为FALSE

二、VBA法（兼容所有Excel版本） #

如果你的Excel不支持动态数组，用VBA脚本批量处理：

1. 按Alt+F11打开VBA编辑器
2. 右键点击工作簿 → 插入 → 模块
3. 粘贴以下代码：

Sub MatchAndConcatenate()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, k As Long
    Dim descArr As Variant, codeArr As Variant
    Dim catStr As String, subCatStr As String, detailStr As String
    
    '指定工作表，按需修改名称
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    '获取数据最后一行
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    '读取Sheet1的CODE及对应数据到数组
    codeArr = ws1.Range("A2:D" & lastRow1).Value
    
    '遍历Sheet2每一行数据
    For i = 2 To lastRow2
        catStr = ""
        subCatStr = ""
        detailStr = ""
        '拆分DESCRIPTION为单个CODE，分隔符按需修改
        descArr = Split(ws2.Cells(i, "A").Value, " ")
        
        '匹配每个CODE并拼接字段
        For Each code In descArr
            code = Trim(code)
            For k = LBound(codeArr) To UBound(codeArr)
                If code = Trim(codeArr(k, 1)) Then
                    If catStr <> "" Then catStr = catStr & "|"
                    catStr = catStr & codeArr(k, 2)
                    
                    If subCatStr <> "" Then subCatStr = subCatStr & "|"
                    subCatStr = subCatStr & codeArr(k, 3)
                    
                    If detailStr <> "" Then detailStr = detailStr & "|"
                    detailStr = detailStr & codeArr(k, 4)
                    Exit For
                End If
            Next k
        Next code
        
        '写入结果到Sheet2
        ws2.Cells(i, "B").Value = catStr
        ws2.Cells(i, "C").Value = subCatStr
        ws2.Cells(i, "D").Value = detailStr
    Next i
    
    MsgBox "处理完成！"
End Sub

4. 修改代码中的工作表名称、分隔符（Split函数里的" "），按F5运行宏即可。

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