Excel跨表多值查找需求：单单元格匹配整列返回对应值

需求回顾 #

• 在单元格D300中查找工作表'FCC-ID-02-VA'的B列所有值
• 若D300包含某个值，返回Sheet1!A:AY区域对应行的第51列（即AY列）的值
• 结果需去重，避免重复输出

原公式局限 #

原公式仅固定匹配'FCC-ID-02-VA'!B18单个单元格，无法遍历整列，也未处理重复值问题。

修改后的公式（动态数组兼容版本） #

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Sheet1!$AY:$AY, ISNUMBER(SEARCH('FCC-ID-02-VA'!$B:$B, D300)), "")))

公式解析 #

1. SEARCH('FCC-ID-02-VA'!$B:$B, D300)：遍历'FCC-ID-02-VA'的B列所有值，检查D300是否包含该值（不区分大小写），返回匹配位置或错误值
2. ISNUMBER(...)：将匹配结果转换为布尔值（匹配成功为TRUE，失败为FALSE）
3. FILTER(Sheet1!$AY:$AY, ...)：根据布尔值筛选Sheet1的AY列（第51列）中对应匹配成功的行
4. UNIQUE(...)：对筛选结果去重，移除重复值
5. TEXTJOIN(", ", TRUE, ...)：将去重后的结果用逗号分隔合并为单个单元格文本，忽略空值

补充说明 #

• 若需严格区分大小写，将SEARCH替换为FIND
• 若'FCC-ID-02-VA'的B列存在空值，可在FILTER的区域中限定非空范围，比如'FCC-ID-02-VA'!$B$2:$B$1000（根据实际数据范围调整）
• 若需返回多个匹配结果的竖排列表，可去掉TEXTJOIN，直接使用UNIQUE(FILTER(...))，公式会自动溢出到下方单元格

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