Excel 2021跨多列区域查找值并返回对应行C列数值的实现方案问询

适配Excel 2021的高效实现方案 #

方案1：XLOOKUP写法（最简洁，自动处理错误）

公式如下，直接粘贴即可使用：

=XLOOKUP(1,MMULT(--($D$1:$F$10=A1),{1;1;1}),$C$1:$C$10,"无匹配",0)

逻辑说明：

• --($D$1:$F$10=A1) 会把检索区域内每个单元格和A1比较，相等返回1，否则返回0
• MMULT(--(...),{1;1;1}) 对每行的比较结果求和，只要该行有任意一列匹配到A1，结果就为1（因为你提到D-F列理论上值唯一，每行最多1个匹配）
• XLOOKUP会找到第一个结果为1的行，返回对应C列的内容，无匹配则返回自定义提示

方案2：INDEX+MATCH写法（兼容原有使用习惯）

如果你更习惯用INDEX+MATCH的组合，可以用以下公式：

=IFERROR(INDEX($C$1:$C$10,MATCH(1,MMULT(--($D$1:$F$10=A1),{1;1;1}),0)),"无匹配")

没有匹配项时默认返回#N/A，套入IFERROR可自定义无匹配的提示内容。 #

特殊场景处理

如果D-F列存在多个行匹配到A1，需要返回所有对应C列内容，可使用动态数组函数FILTER，结果会自动溢出：

=FILTER($C$1:$C$10,MMULT(--($D$1:$F$10=A1),{1;1;1})=1,"无匹配")

性能说明

以上公式均未使用易失函数，数组运算效率高，适配5万行规模的表格无卡顿问题。如果后续需要扩展检索列数，只需要把{1;1;1}修改为对应列数的纵向1数组即可，比如扩展到4列就改为{1;1;1;1}。

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