Excel 2016跨表查找同设备多匹配结果并拼接至同一单元格

需求说明 #

现有工作表List_State_6.10.2022，表结构参考如下：

A                    G         ...  J          ...  S
1    Device    Org   ...  component ...  Display    ...  Comp+Display
     ABC123    co    ...  part1     ...  Not Found  ...  part1+Not Found
     ABC234    co    ...   part2    ...  ok         ...  part2+ok
     ABC123    co    ...   part3    ...  ok         ...  part3+ok

需要在FinalResult工作表的W2单元格实现多值匹配：提取A列设备编号和目标值一致的所有行对应的S列Comp+Display字段值，用分号拼接。例如设备ABC123的预期返回结果为part1+Not Found;part3+ok，方案需兼容Excel 2016版本，不使用365专属的动态数组函数。

实现方法 #

方法1：原生数组公式（无宏，适合预装TEXTJOIN的Excel 2016版本） #

大部分零售版Excel 2016自带TEXTJOIN函数，直接在W2单元格输入以下公式，输入完成后不要直接按回车，按住Ctrl+Shift三键同按，触发数组计算即可：

=TEXTJOIN(";",TRUE,IF(List_State_6.10.2022!A:A="ABC123",List_State_6.10.2022!S:S,""))

• 公式中"ABC123"可替换为存储目标设备编号的单元格引用，比如目标编号在W1单元格，直接替换为W1即可
• 公式逻辑为：遍历A列所有值，匹配到和目标编号一致的行就取对应S列的值，否则返回空值，最后用分号把所有非空结果拼接起来

方法2：VBA自定义函数（全版本Excel 2016兼容） #

如果你使用的是VOL批量授权版Excel 2016（无内置TEXTJOIN函数），用自定义函数实现更稳定，步骤如下：

1. 按Alt+F11快捷键打开VBA编辑器
2. 左侧工程面板右键点击当前工作簿名称，依次选择「插入」-「模块」
3. 在弹出的代码编辑区粘贴以下代码：

Function ConcatMatch(matchVal As Range, matchRng As Range, concatRng As Range, Optional sep As String = ";") As String
    Dim i As Long
    Dim res As String
    res = ""
    For i = 1 To matchRng.Rows.Count
        If matchRng.Cells(i, 1).Value = matchVal.Value Then
            res = res & concatRng.Cells(i, 1).Value & sep
        End If
    Next i
    If Len(res) > 0 Then
        ConcatMatch = Left(res, Len(res) - Len(sep))
    End If
End Function

4. 关闭VBA编辑器回到工作表界面，在W2单元格直接输入以下公式，按普通回车即可得到结果：

=ConcatMatch(目标设备编号所在单元格, List_State_6.10.2022!A:A, List_State_6.10.2022!S:S)

注意：使用该方法需要将工作簿保存为.xlsm启用宏格式，下次打开文件时选择启用宏，函数才能正常计算。

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