如何在Excel中批量返回指定值列表对应的所有单元格位置？

方法1：Excel 365/2021 动态数组公式 #

如果你的Excel支持动态数组（365或2021版本），可以用以下公式直接得到所有匹配的单元格地址，结果会自动溢出显示：

=FILTER(ADDRESS(ROW(A10:D500), COLUMN(A10:D500), 4), ISNUMBER(XMATCH(A10:D500, A1:A3)))

如果需要把结果合并成一行（用逗号分隔），使用：

=TEXTJOIN(", ", TRUE, FILTER(ADDRESS(ROW(A10:D500), COLUMN(A10:D500), 4), ISNUMBER(XMATCH(A10:D500, A1:A3))))

公式说明：

• XMATCH(A10:D500, A1:A3)：检测每个单元格值是否在目标列表（A1:A3）中，匹配返回位置，不匹配返回错误值
• ISNUMBER(...)：将匹配结果转换为布尔值（TRUE=匹配，FALSE=不匹配）
• ADDRESS(ROW(...), COLUMN(...), 4)：生成单元格的相对地址（如A16），参数4指定使用相对引用格式
• FILTER：筛选出所有符合条件的地址，动态数组自动将结果逐行显示
• TEXTJOIN：将所有结果合并为一行，用指定分隔符连接

方法2：Excel 2019及更早版本（非动态数组） #

对于不支持动态数组的版本，使用数组公式结合SMALL函数逐行提取结果：

在空白单元格（如E1）输入以下公式，按Ctrl+Shift+Enter作为数组公式提交，然后下拉填充直到出现空值：

=IFERROR(ADDRESS(SMALL(IF(ISNUMBER(XMATCH(A10:D500, A1:A3)), ROW(A10:D500)), ROW(A1)), SMALL(IF(ISNUMBER(XMATCH(A10:D500, A1:A3)), COLUMN(A10:D500)), ROW(A1)), 4), "")

公式说明：

• IF(ISNUMBER(XMATCH(...)), ROW(...))：收集所有匹配单元格的行号，不匹配的返回FALSE
• SMALL(..., ROW(A1))：依次提取第1、2、3...个匹配单元格的行号（下拉时ROW(A1)自动变为ROW(A2)、ROW(A3)等）
• 同理提取列号，通过ADDRESS生成单元格地址
• IFERROR：当没有更多匹配结果时，返回空值避免错误显示

方法3：VBA脚本（适合大量数据或自动化需求） #

如果处理的数据量较大，或者需要重复执行搜索，使用VBA脚本更高效：

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

Sub GetMatchingCellAddresses()
    Dim searchRng As Range, targetList As Range
    Dim cell As Range
    Dim outputCol As Range
    Dim resultRow As Integer
    
    ' 定义搜索区域、目标值列表和结果输出起始单元格
    Set searchRng = ThisWorkbook.Sheets("Sheet1").Range("A10:D500")
    Set targetList = ThisWorkbook.Sheets("Sheet1").Range("A1:A3")
    Set outputCol = ThisWorkbook.Sheets("Sheet1").Range("E1")
    resultRow = 1
    
    ' 清空输出区域旧数据
    outputCol.CurrentRegion.ClearContents
    
    ' 遍历搜索区域，收集匹配地址
    For Each cell In searchRng
        If Not IsError(Application.Match(cell.Value, targetList, 0)) Then
            outputCol.Offset(resultRow - 1, 0).Value = cell.Address(False, False)
            resultRow = resultRow + 1
        End If
    Next cell
    
    MsgBox "搜索完成，共找到" & resultRow - 1 & "个匹配单元格"
End Sub

4. 按F5运行脚本，结果会输出到指定的单元格区域（示例中为E列起始）

脚本说明：

• 可根据实际需求修改searchRng、targetList和outputCol的范围
• 运行前会自动清空输出区域的旧数据
• 遍历结束后会弹出提示框显示匹配数量

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