Return the largest value (and corresponding label)

Question

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!

Answer 1

Try adding a helper column to get your result.

I inserted a helper column into Column A that includes the formula

=IF($D2=2017,COUNTIF($B$2:$B$21,">="&$B2),"")

where $B$2:$B$21 is the full column of data in Value ($M), $B2 is the first cell under Value ($M), and 2017 is the year for which you're pulling data, so adjust each as needed. Drag this formula down the entire length of your helper column.
Side note: If the year you are trying to pull for can change, consider adding a dedicated cell that is meant for holding the requested year. This cell will then replace 2017 in my suggested formula.

Then in $G$1 enter the following formula:

=INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,$F2),$A$2:$A$21,0),2)

where, again, $B$2:$B$21 is the full column of data in Value ($M), $F2 is the value of 1 under Rank, and $A$2:$A$21 is the full column of data in the helper column. Drag this formula down for the other two rows in Rank.

You can then use the same formula in Column H, but only change the 2 at the very end of the formula to 1, like so:

=INDEX($B$2:$C$21,MATCH(SMALL($A$2:$A$21,$F2),$A$2:$A$21,0),1).

If you end up having multiple contracts in the same year with the exact same Value ($M), it's going to place them in a rank order according to which shows up in the list first.

Answer 2

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.