如何在Excel多行列区域定位最值对应的行与列标题？

针对你的跨年度销售数据最值匹配问题，以下是适配多列多行场景的Excel公式方案，同时解决INDEX/MATCH的#VALUE错误问题：

一、已知目标年份（如2022），匹配该年份最值对应的日期与年份列 #

假设数据范围定义：

• 日期列：$A$2:$A$1000（可根据实际行数调整）
• 年份标题行：$B$1:$Z$1（可根据实际列数调整）
• 销售数据区域：$B$2:$Z$1000

1. 定位目标年份对应的列 #

若年份标题含额外字符（如销售_2022），用模糊匹配定位列位置：

=MATCH("*2022*", $B$1:$Z$1, 0)

2. 提取目标年份的最小值 #

忽略非数值单元格，避免错误：

=AGGREGATE(15, 6, INDEX($B$2:$Z$1000, 0, MATCH("*2022*", $B$1:$Z$1, 0))/ISNUMBER(INDEX($B$2:$Z$1000, 0, MATCH("*2022*", $B$1:$Z$1, 0))), 1)

3. 匹配最小值对应的日期 #

=INDEX($A$2:$A$1000, MATCH(
    AGGREGATE(15, 6, INDEX($B$2:$Z$1000, 0, MATCH("*2022*", $B$1:$Z$1, 0))/ISNUMBER(INDEX($B$2:$Z$1000, 0, MATCH("*2022*", $B$1:$Z$1, 0))), 1),
    INDEX($B$2:$Z$1000, 0, MATCH("*2022*", $B$1:$Z$1, 0)),
    0
))

4. 提取完整的年份标题（含额外字符） #

=INDEX($B$1:$Z$1, MATCH("*2022*", $B$1:$Z$1, 0))

二、全局匹配所有年份的最值，返回对应日期与年份 #

若需找整个数据区域的最值，用以下公式：

1. 找最值对应的日期 #

=INDEX($A$2:$A$1000, SUMPRODUCT((($B$2:$Z$1000=MIN($B$2:$Z$1000))*ROW($B$2:$Z$1000)))-1)

注：若存在多个相同最值，返回第一个出现的日期；Excel 365+可改用FILTER($A$2:$A$1000, $B$2:$Z$1000=MIN($B$2:$Z$1000))返回所有匹配项

2. 找最值对应的年份标题 #

=INDEX($B$1:$Z$1, SUMPRODUCT((($B$2:$Z$1000=MIN($B$2:$Z$1000))*COLUMN($B$2:$Z$1000)))-1)

三、解决INDEX/MATCH的#VALUE错误 #

错误原因及修复：

• 匹配区域大小不一致：确保INDEX的行范围与MATCH返回的行号对应，避免混用标题行与数据行
• 精确匹配失效：年份标题带额外字符时，必须用通配符*做模糊匹配，而非精确匹配（MATCH第三参数为0）
• 非数值单元格干扰：用AGGREGATE替代MIN，忽略空值、文本等非数值单元格

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