Index Match Aggregate - find largest or 2nd largest value

willow1985

Active Member
Joined
Jul 24, 2019
Messages
320
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
320
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,811
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,811
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
320
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,811
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,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top