Google Sheets中如何在整个工作表精准匹配单元格并返回地址

问题描述 #

• 需求：在当前工作表中，搜索整个Dropdowns工作表，找到与B1单元格内容完全匹配的单元格地址。
• 当前使用公式：

  =ARRAYFORMULA(ADDRESS(LARGE(ISNUMBER(SEARCH(B1,Dropdowns!A:Z))*ROW(Dropdowns!A:Z),1),LARGE(ISNUMBER(SEARCH(B1,Dropdowns!A:Z))*COLUMN(Dropdowns!A:Z),1)))
  

• 存在问题：该公式通过SEARCH执行模糊匹配（只要单元格包含目标字符串即判定匹配），仅能返回包含目标字符串的最后一个单元格地址，无法满足精确匹配需求。
• 用户疑问：是否应使用QUERY函数来实现精确匹配？

解决方案 #

可以使用QUERY函数，但也有更简洁高效的直接公式实现精确匹配：

方法1：直接用IF+ARRAYFORMULA（推荐） #

返回第一个匹配的单元格地址

=ARRAYFORMULA(ADDRESS(MIN(IF(Dropdowns!A:Z=B1,ROW(Dropdowns!A:Z),)),MIN(IF(Dropdowns!A:Z=B1,COLUMN(Dropdowns!A:Z),))))

• 原理：用Dropdowns!A:Z=B1判断精确匹配，通过MIN提取第一个匹配的行号和列号，再用ADDRESS生成单元格地址。
• 效果：若存在多个匹配，返回行号最小、列号最小的单元格地址。

返回所有匹配的单元格地址

=ARRAYFORMULA(IFERROR(ADDRESS(ROW(Dropdowns!A:Z),COLUMN(Dropdowns!A:Z))*(Dropdowns!A:Z=B1),""))

• 效果：在Dropdowns工作表对应位置显示匹配的单元格地址，不匹配的单元格显示空值。

方法2：使用QUERY函数 #

=ARRAYFORMULA(ADDRESS(
  QUERY(FLATTEN(ROW(Dropdowns!A:Z)&"|"&COLUMN(Dropdowns!A:Z)&"|"&Dropdowns!A:Z),
        "select Col1 where Col3='"&B1&"' limit 1 offset 0",0),
  QUERY(FLATTEN(ROW(Dropdowns!A:Z)&"|"&COLUMN(Dropdowns!A:Z)&"|"&Dropdowns!A:Z),
        "select Col2 where Col3='"&B1&"' limit 1 offset 0",0)
))

• 原理：先通过FLATTEN将行号、列号、单元格内容合并为一维数据，再用QUERY筛选出内容等于B1的条目，提取对应行号和列号后生成单元格地址。
• 注意：若要返回其他匹配项，可修改offset参数（如offset 1返回第二个匹配项）。

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