Google Sheets如何返回查找结果对应的全部匹配单元格引用地址

解决方案 #

你可以通过FILTER+BYROW+ADDRESS的组合公式实现需求，新版Google Sheets支持自动横向溢出所有匹配地址，无需手动拖拽公式。

自动溢出版本（推荐，适用于2022年之后的Google Sheets版本）

在你需要显示首个匹配地址的单元格（例如原公式所在的B4单元格）输入以下公式即可：

=IF(A4="",,BYROW(FILTER(ROW('Editorial 2022'!$S$3:$S$100),'Editorial 2022'!$S$3:$S$100=A4),LAMBDA(r,ADDRESS(r,COLUMN('Editorial 2022'!S:S),4,TRUE,"Editorial 2022"))))

公式说明：

• FILTER部分会筛选出Editorial 2022表S列所有和A4值匹配的行号
• BYROW遍历所有筛选出的行号，调用ADDRESS生成带工作表名的完整单元格地址
• ADDRESS的第三个参数设为4代表返回相对引用格式，需要绝对引用可改为1
• 公式会自动将所有匹配地址横向溢出到右侧单元格，匹配多少个就自动占多少列

手动右拉版本（适用于旧版不支持溢出的表格版本）

在首个匹配地址单元格输入以下公式，然后向右拖拽到足够多的列即可：

=IFERROR(ADDRESS(SMALL(FILTER(ROW('Editorial 2022'!$S$3:$S$100),'Editorial 2022'!$S$3:$S$100=$A4),COLUMN(A1)),COLUMN('Editorial 2022'!S:S),4,TRUE,"Editorial 2022"),"")

公式说明：

• 右拉时COLUMN(A1)会自动递增为COLUMN(B1)、COLUMN(C1)，依次提取第2、第3个匹配结果的行号
• 没有更多匹配结果时会自动返回空值，不会显示错误信息

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