根据给定条件返回列中的最大值(和相应的标签) [英] Return the largest value (and corresponding label) in a column based on given criteria

问题描述

因此，我有一个包含三列的Excel工作表:[合同授予]的价值($ M)，合同名称和年份.

数据图像:

我正在尝试返回具有3个最大价值的合同的合同名称.我在

So I have an Excel sheet with three columns: Value ($M), Contract Name, and Year [of Contract Award].

Image of Data:

I am trying to return the Contract Name of the contracts with the 3 largest values. I found the solution to that here.

=INDEX($B$2:$B$21,MATCH(1,INDEX(($A$2:$A$21=LARGE($A$2:$A$21,E2))*(COUNTIF(E2,$B$2:$B$21)=0),),))

However, I have an added caveat: I only want to pull contracts from 2017. I found the solution for pulling the values from 2017 here using a CSE formula and adjusted the formula accordingly:

{=INDEX($B$2:$B$21,MATCH(1,INDEX(($A$2:$A$21=LARGE(IF($C$2:$C$21=2017,$A$2:$A$21),E2))*(COUNTIF(E2,$B$2:$B$21)=0),),))}

When I incorporate that into the formula it works fine on returning only the top 3 values from 2017, however because one of the top 3 values from 2017 is a duplicate of a 2015 value, it is picking up the contract name from 2015.

Image of Results:

As shown on the above image, it is pulling "Contract 1" from 2015 for $100M as opposed to "Contract 8" from 2017 for $100M. Any idea how to adjust my formula to only pick up the contract names from 2017? Thanks in advance!

解决方案

You can use this formula in cell F2. There is no need for helper column.

=INDEX($B$2:$B$21,MATCH(LARGE(IF($C$2:$C$21=2017,$A$2:$A$21,0), ROW()-1), IF($C$2:$C$21=2017,$A$2:$A$21,0), 0),1)

Since this is an array formula, please confirm it with CTRL+SHIFT+ENTER.

这篇关于根据给定条件返回列中的最大值(和相应的标签)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！