旧版Excel修改公式：实现多匹配值对应列标以分号分隔返回

针对你需要提取Sheet2对应行中所有值为1的列编号（以;分隔）的需求，由于旧版Excel无法使用FILTER函数，可采用以下两种数组公式方案（需按Ctrl+Shift+Enter完成输入，不能直接按Enter）：

方案1：无TEXTJOIN函数的超旧版Excel（如2016及更早） #

使用SMALL+TEXT+LEFT组合实现拼接：

=IFERROR(LEFT(TEXT(SMALL(IF(INDEX(Sheet2!$B$2:$G$9,MATCH(A2,Sheet2!$A$2:$A$9,0),0)=1,{1,2,3,4,5,6}),ROW(INDIRECT("1:"&COUNTIF(INDEX(Sheet2!$B$2:$G$9,MATCH(A2,Sheet2!$A$2:$A$9,0),0),1))),"0;"),LEN(TEXT(SMALL(IF(INDEX(Sheet2!$B$2:$G$9,MATCH(A2,Sheet2!$A$2:$A$9,0),0)=1,{1,2,3,4,5,6}),ROW(INDIRECT("1:"&COUNTIF(INDEX(Sheet2!$B$2:$G$9,MATCH(A2,Sheet2!$A$2:$A$9,0),0),1))),"0;"))-1),"No Match")

公式逻辑： #

1. MATCH(A2,Sheet2!$A$2:$A$9,0)：定位Sheet2中对应Name的行号
2. INDEX(Sheet2!$B$2:$G$9,...)：提取该行B-G列的所有数据
3. IF(...,{1,2,3,4,5,6})：将该行中值为1的位置替换为对应列编号（B=1、C=2…G=6），其余为FALSE
4. SMALL(...,ROW(INDIRECT(...)))：通过SMALL逐个提取所有有效列编号，COUNTIF统计该行中1的数量，确保提取所有匹配项
5. TEXT(...,"0;")：将每个编号格式化为「数字;」的文本
6. LEFT(...,LEN(...)-1)：去掉最后一个多余的分号
7. IFERROR：无匹配时返回No Match

方案2：支持TEXTJOIN函数的旧版Excel（如2019或部分Office 365版本） #

使用TEXTJOIN简化拼接逻辑，公式更简洁：

=IFERROR(TEXTJOIN(";",TRUE,IF(INDEX(Sheet2!$B$2:$G$9,MATCH(A2,Sheet2!$A$2:$A$9,0),0)=1,{1,2,3,4,5,6},"")),"No Match")

公式逻辑： #

1. 前两步与方案1一致，定位行并提取数据
2. IF(...,{1,2,3,4,5,6})：生成包含匹配列编号和空值的数组
3. TEXTJOIN(";",TRUE,...)：用;拼接所有非空的列编号，TRUE参数自动忽略空值
4. IFERROR：无匹配时返回No Match

注意事项： #

• 公式中的{1,2,3,4,5,6}需与Sheet2的目标列范围（B-G）一一对应，若列范围调整，需同步修改此数组
• 必须按Ctrl+Shift+Enter触发数组计算，否则无法正确返回所有匹配项

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