如何在Excel中使用FILTER函数返回搜索结果所在工作表名称？

动态数组公式（Excel 365/2021 推荐） #

在K3作为搜索栏的前提下，直接在K6单元格输入以下公式，它会自动遍历所有Region 1到Region 20的工作表，返回所有包含目标学区（模糊匹配）的区域名称：

=FILTER("Region "&SEQUENCE(20),BYROW("Region "&SEQUENCE(20),LAMBDA(sheet,SUMPRODUCT(--ISNUMBER(SEARCH(K3,INDIRECT("'"&sheet&"'!B2:B39"))))>0)),"Not Found")

公式拆解 #

• "Region "&SEQUENCE(20)：自动生成Region 1到Region 20的工作表名称数组
• BYROW(..., LAMBDA(sheet, ...))：逐个遍历每个工作表名称，执行匹配检查
• INDIRECT("'"&sheet&"'!B2:B39")：动态引用对应工作表的B2:B39学区区域
• SEARCH(K3, ...)：实现不区分大小写的模糊匹配，找到目标学区的位置或返回错误
• ISNUMBER(...)：将匹配结果转换为布尔值（匹配为TRUE，不匹配为FALSE）
• --：把布尔值转为1/0，方便统计匹配数量
• SUMPRODUCT(...)>0：判断当前工作表中是否存在至少一个匹配项
• FILTER：筛选出所有符合条件的区域名称，无匹配时返回Not Found

旧版Excel兼容方案（无动态数组支持） #

如果使用Excel 2019及更早版本，可使用以下数组公式（输入后按Ctrl+Shift+Enter确认），结果会以逗号分隔显示所有匹配区域：

=IFERROR(TEXTJOIN(", ",TRUE,IF(SUMPRODUCT(--ISNUMBER(SEARCH(K3,INDIRECT("'Region "&ROW(INDIRECT("1:20"))&"'!B2:B39"))))>0,"Region "&ROW(INDIRECT("1:20")),)),"Not Found")

注意事项 #

• 确认所有Region X工作表的学区数据都在B2:B39范围，若不同工作表范围有差异，可调整为'&sheet&"'!B:B（但会降低公式性能，建议用准确范围）
• 若需要区分大小写的模糊匹配，将公式中的SEARCH替换为FIND
• 动态数组公式会自动溢出显示多个结果，无需手动下拉填充

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