Excel公式求助：为Sheet2重复项目返回Sheet1对应最高值参数名

我来帮你搞定这个需求，先明确下咱们的数据结构（你可以根据实际情况调整范围）：

• Sheet1：A列是参数名（比如从A2开始到A100），第1行是项目名（B1到Z1），交叉的单元格是对应数值（B2到Z100）
• Sheet2：A列是重复的项目列表（不能排序，这点完全没问题，公式不依赖排序），咱们要在B列给每个项目返回Sheet1里该项目下数值最高的参数名

核心公式（直接用在Sheet2的B2单元格） #

=INDEX(Sheet1!$A$2:$A$100, MATCH(MAXIFS(Sheet1!$B$2:$Z$100, Sheet1!$B$1:$Z$1, Sheet2!A2), INDEX(Sheet1!$B$2:$Z$100, 0, MATCH(Sheet2!A2, Sheet1!$B$1:$Z$1, 0)), 0))

输入完直接下拉填充，所有项目的结果就出来了。

公式拆解（怕你看不懂，给你拆成小块讲） #

1. MAXIFS(Sheet1!$B$2:$Z$100, Sheet1!$B$1:$Z$1, Sheet2!A2)：先找到Sheet1里当前项目（就是Sheet2 A2的内容）对应的所有数值里的最大值
2. MATCH(Sheet2!A2, Sheet1!$B$1:$Z$1, 0)：定位这个项目在Sheet1第1行的列位置，比如项目在C列，就返回3
3. INDEX(Sheet1!$B$2:$Z$100, 0, ...)：提取Sheet1里这个项目对应的整列数值，比如刚才的C列，就取C2到C100
4. MATCH(MAXIFS的结果, 刚才提取的整列数值, 0)：找到最大值在这一列里是第几行
5. INDEX(Sheet1!$A$2:$A$100, ...)：最后根据这个行号，返回Sheet1 A列对应的参数名

优化：处理找不到项目的情况 #

如果Sheet2里的项目在Sheet1中不存在，公式会显示#N/A，看着不太友好，咱们可以加个IFERROR改成自定义提示：

=IFERROR(INDEX(Sheet1!$A$2:$A$100, MATCH(MAXIFS(Sheet1!$B$2:$Z$100, Sheet1!$B$1:$Z$1, Sheet2!A2), INDEX(Sheet1!$B$2:$Z$100, 0, MATCH(Sheet2!A2, Sheet1!$B$1:$Z$1, 0)), 0)), "无匹配项目")

几个注意点 #

• 一定要根据你实际的表格范围调整公式里的单元格区域！比如如果Sheet1的参数名到A200，数值到Z200，就把$A$2:$A$100改成$A$2:$A$200，$B$2:$Z$100改成$B$2:$Z$200
• 如果同一个项目下有多个参数的数值都是最大值，这个公式会返回第一个出现的那个参数名；要是你想把所有符合的参数名都列出来，Excel 365/2021可以用TEXTJOIN配合数组公式实现，需要的话可以再问我

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