如何对比Excel两列数据并在单个单元格返回所有匹配值？

有公式仅能返回两列中的首个匹配值，比如：
=INDEX($A$1:$A$5,MATCH(TRUE,COUNTIF($B$1:$B$5,$A$1:$A$5)>0,0))

但我们需要将两列中所有匹配值汇总到单个单元格，无匹配时单元格为空，以下是具体实现方案：

需求示例 #

数据源 #

预期结果 #

解决方案 #

Excel 365/2021及以上版本（支持TEXTJOIN函数） #

直接使用以下公式（回车即可，无需数组快捷键）：

=IFERROR("Matched: "&TEXTJOIN(", ",TRUE,IF(COUNTIF($B$1:$B$3,$A$1:$A$3)>0,$A$1:$A$3,"")),"")

• 逻辑说明：
  1. COUNTIF($B$1:$B$3,$A$1:$A$3)>0 识别A列中存在于B列的单元格
  2. TEXTJOIN(", ",TRUE,...) 将所有匹配值用「逗号+空格」拼接
  3. IFERROR(..., "") 无匹配值时返回空单元格

旧版Excel（无TEXTJOIN函数） #

可通过VBA自定义函数实现：

1. 按 Alt+F11 打开VBA编辑器
2. 插入新模块，粘贴以下代码：

Function GetMatchedValues(rngA As Range, rngB As Range) As String
    Dim cell As Range
    Dim result As String
    result = ""
    For Each cell In rngA
        If Not IsError(Application.Match(cell.Value, rngB, 0)) Then
            If result <> "" Then result = result & ", "
            result = result & cell.Value
        End If
    Next cell
    GetMatchedValues = IIf(result <> "", "Matched: " & result, "")
End Function

3. 返回Excel，在目标单元格输入：=GetMatchedValues($A$1:$A$3,$B$1:$B$3)

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