Excel中用INDEX/MATCH/MAX匹配指定公司行内最高值对应指标的公式问题

为啥你之前的公式结果不对 #

你之前写的公式有三个逻辑错位的地方，直接导致匹配结果完全不符合预期：

• 要的是指标名称，结果INDEX取的是数值区：你最终需要返回的是第一行的Metric 1/2/3这类名称，但你INDEX的第一参数选的是B2:D6的指标数值区，就算位置算对了，最后返回的也是具体数字，不是指标名，从根上取错了区域。
• 最大值取了全表的，不是目标行的：你用MAX(B2:D6)算出来的是所有公司、所有指标的全局最大值，不是你选定那家公司对应行里的三个指标的最大值。比如你查Company 1的时候，全表最大值是Company 2的5.0，公式会顺着这个值去找列位置，和你要查的Company 1完全没关系。
• 匹配列位置的参数用错了：你找最大值位置的MATCH部分，范围跨了好几行，还把匹配参数设成了1——这个参数是给升序排序的范围做近似匹配用的，你现在的目标行数值是乱序的，算出来的位置全是错的。

改完就能用的公式 #

按照你的表结构（A列第8行写“Company”当标签，B8单元格放选定的待查询公司），全版本Excel都能跑的兼容公式是：
=INDEX($B$1:$D$1, MATCH(MAX(OFFSET($A$1, MATCH($B$8, $A$2:$A$6, 0), 1, 1, 3)), OFFSET($A$1, MATCH($B$8, $A$2:$A$6, 0), 1, 1, 3), 0))

公式运行逻辑 #

• 开头的MATCH($B$8, $A$2:$A$6, 0)就是你之前测试正常的行定位逻辑，完全保留，返回选定公司在列表里的相对行号。
• 里面两处相同的OFFSET，就是根据刚才算出来的行号，直接跳到目标公司那一行，框住该行B到D列的三个指标值，全程不会碰到其他公司的数据。
• MAX直接对刚才框出来的三个目标行数值算最大值，不会混进别的公司的数。
• 内层的MATCH用精确匹配（参数0）在这三个目标值里找最大值的位置，返回1/2/3的列序号。
• 外层INDEX就从B1:D1的指标表头里，按这个序号拿出对应的指标名称，就是你要的结果。

小补充 #

• 这个公式能完美覆盖你举的示例场景：选Company 1返回Metric 2，选Company 2返回Metric 1。
• 如果某一行有两个并列的最高值，公式会默认返回最左侧的那个指标名，需要调整返回优先级的话可以再微调MATCH逻辑。
• 如果你用的是Excel 365/2021以上的版本，可以用更省计算的动态数组写法替代易失的OFFSET：
  =INDEX(B1:D1, MATCH(MAX(TOCOL(B2:D6*(A2:A6=B8))), TOCOL(B2:D6*(A2:A6=B8)), 0))

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