Index Match Aggregate - find largest or 2nd largest value

willow1985

Active Member
Joined
Jul 24, 2019
Messages
316
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
 
Last edited:

willow1985

Active Member
Joined
Jul 24, 2019
Messages
316
Figured it out. Need to use LARGE instead.

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,803
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,803
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

Active Member
Joined
Jul 24, 2019
Messages
316
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,803
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.
 

Forum statistics

Threads
1,078,348
Messages
5,339,688
Members
399,319
Latest member
JOSEILLO

Some videos you may like

This Week's Hot Topics

Top