Index Match Aggregate - find largest or 2nd largest value

[willow1985]

I have a formula that compares 2 columns and returns the largest value:

=INDEX(AH2:AH500,MATCH(MAX(AI2:AI500),AI2:AI500,0))

I would like this formula modified to return the 2nd or even 3rd largest value instead of just the MAX but am not sure how to modify it/apply Aggregate. I am hoping someone on here can help.

This formula will not work (I have not used the AGGREGATE formula much)

=INDEX(AH2:AH500,MATCH(AGGREGATE(14,6,AI2:AI500),AI2:AI500,2))

Thank you to anyone who can help.

Note: Data is Names in column AH and numbers in column AI

Carla

 

[willow1985]

Figured it out. Need to use LARGE instead.

=INDEX(AH2:AH500,MATCH(LARGE(AI2:AI500,2),AI2:AI500,0))

 

[Eric W]

Do you need to worry about ties? If you have values of 100, 95, 95, 90 for example, and you want to get the names that go with both of the 95 numbers, it's a bit trickier.

 

[Eric W]

For example:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Score</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">Score</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Amy</td><td style="text-align: right;;">85</td><td style="text-align: right;;"></td><td style=";">Cheryl</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Bill</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style=";">Giselle</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Cheryl</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Bill</td><td style="text-align: right;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Doug</td><td style="text-align: right;;">85</td><td style="text-align: right;;"></td><td style=";">Amy</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Edie</td><td style="text-align: right;;">75</td><td style="text-align: right;;"></td><td style=";">Doug</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Fred</td><td style="text-align: right;;">85</td><td style="text-align: right;;"></td><td style=";">Fred</td><td style="text-align: right;;">85</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Giselle</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Edie</td><td style="text-align: right;;">75</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AK2</th><td style="text-align:left">=INDEX(<font color="Blue">$AH:$AH,AGGREGATE(<font color="Red">15,6,ROW(<font color="Green">$AH$2:$AH8</font>)/(<font color="Green">$AI$2:$AI$8=AL2</font>),COUNTIF(<font color="Green">$AL$2:$AL2,AL2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AL2</th><td style="text-align:left">=LARGE(<font color="Blue">$AI$2:$AI$8,ROWS(<font color="Red">$AL$2:$AL2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[willow1985]

It would be very rare if there was a tie but I suppose it is something to consider. How would that work? would it return both matches?

 

[Eric W]

Well, consider the list of values I made in post 4: 85,90,100,85,75,85,100. If you do LARGE(AI2:AI8,1) you get 100. If you do LARGE(AI2:AI8,2), you also get 100. You can see the whole list in the AL column. So if you use your formula

=INDEX(AH2:AH500,MATCH(LARGE(AI2:AI500,1),AI2:AI500,0))

with a 1, it would return Cheryl, since LARGE returns 100, and the MATCH first finds 100 in row 4. If you use your formula

=INDEX(AH2:AH500,MATCH(LARGE(AI2:AI500,2),AI2:AI500,0))

with a 2, it still returns Cheryl, since LARGE returns 100, and the MATCH first finds 100 in row 4. MATCH has no way of knowing you want the second 100. The AK2 formula I provided looks for the nth instance of the LARGE value. The AL2 reference points to the LARGE function, and the COUNTIF checks to see how many matching names/values have already been printed. By changing the AL2 reference directly to a LARGE, and the COUNTIF to a number, you could use the formula in a standalone way without dragging down the column. It just depends on what your requirements are.