Index Match Aggregate - find largest or 2nd largest value

willow1985

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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:

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

willow1985

Active Member
Joined
Jul 24, 2019
Messages
438
Office Version
2019
Platform
Windows
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
9,319
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
9,319
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
438
Office Version
2019
Platform
Windows
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
9,319
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,118
Messages
5,412,538
Members
403,432
Latest member
cr2141

This Week's Hot Topics

Top