如何编写Excel函数，匹配列值后取最高价格对应的目标列？

你之前用VLOOKUP、IF组合无法实现的原因是VLOOKUP默认返回同条件下第一个匹配到的行结果，无法直接筛选出最大值对应的记录，需要先通过聚合函数拿到目标Test分组下的最大price，再反向匹配行数据即可。

解决方案 #

适用Excel 365/2021及以上版本 #

先做如下引用约定，你可以根据自己实际的表格列位置调整：

• 表1Test列数据位于Sheet1!A2:A[n]
• 表2结构：A列Test、B列price、C列Value1、D列rank，数据范围为Sheet2!A2:D[n]

你需要的两个返回值可以直接用以下公式实现：

• 返回对应Value1：
  =XLOOKUP(MAXIFS(Sheet2!$B:$B,Sheet2!$A:$A,Sheet1!A2),Sheet2!$B:$B,Sheet2!$C:$C,"无匹配")
• 返回对应rank：
  =XLOOKUP(MAXIFS(Sheet2!$B:$B,Sheet2!$A:$A,Sheet1!A2),Sheet2!$B:$B,Sheet2!$D:$D,"无匹配")

公式逻辑：

1. 先用MAXIFS函数筛选出表2中Test值与表1当前行Test值相等的所有行，提取这些行的price最大值
2. 再用XLOOKUP匹配该最大值对应的行，返回你指定的列数据

适用Excel 2019及更早版本 #

旧版本不支持XLOOKUP，可使用INDEX+MATCH数组公式实现，输入完成后需要按Ctrl+Shift+Enter确认数组生效：

• 返回对应Value1：
  =INDEX(Sheet2!$C:$C,MATCH(MAX(IF(Sheet2!$A:$A=Sheet1!A2,Sheet2!$B:$B,0)),Sheet2!$B:$B,0))
• 返回对应rank：
  =INDEX(Sheet2!$D:$D,MATCH(MAX(IF(Sheet2!$A:$A=Sheet1!A2,Sheet2!$B:$B,0)),Sheet2!$B:$B,0))

注意事项 #

如果出现同一个Test值对应多条相同最大price的记录，上述公式会返回第一条匹配到的行的数据，如有去重或多值返回需求可额外增加判断条件。

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