Office 365 Excel借助INDEX MATCH随机返回匹配项对应非空列标题

解决方案 #

首先做如下引用约定，你可以根据自己的实际表格范围调整对应参数：

• 表格B的水果名称列：F2:F100（覆盖表格B所有水果行）
• 表格B的属性列标题行：G1:Z1（覆盖表格B所有属性列的表头）
• 表格B的属性值区域：G2:Z100（覆盖水果行和属性列交叉的所有值）
• 表格A当前行待匹配的水果单元格为A2，公式写在表格A的B2单元格

可用公式（Office 365原生支持，无需数组三键） #

=LET(
  匹配行号, XMATCH(A2, F2:F100, 0),
  该行属性值, INDEX(G2:Z100, 匹配行号, ),
  有效表头, FILTER(G1:Z1, 该行属性值<>""),
  INDEX(有效表头, RANDBETWEEN(1, COUNTA(有效表头)))
)

逻辑说明 #

1. 用XMATCH精确定位到表格B中与当前水果匹配的行号，解决两个表格排序不一致的匹配问题
2. 用INDEX提取出匹配行的所有属性值
3. 用FILTER筛选出属性值非空对应的表头，得到所有可选的有效结果集
4. 用RANDBETWEEN生成随机索引，从有效结果集中随机取出一个表头返回

注意事项 #

• 如果习惯用传统MATCH函数，把公式里的XMATCH替换为MATCH即可，第三个参数保持0做精确匹配，效果完全一致
• 如果匹配到的水果在表格B中所有属性都为空，公式会返回#CALC!错误，可嵌套IFERROR处理异常，比如返回空值：

  =IFERROR(上面的完整公式, "")
  

• 公式写完直接回车即可生效，下拉即可批量应用到表格A的所有行
• 如果你使用的是Excel结构化表，替换为结构化引用（如表B[水果名称]、表B[#标题]）会更稳定，调整表格范围时不需要手动改公式参数

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