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:

Book1
	AH	AI	AJ	AK	AL
1	Name	Score		Name	Score
2	Amy	85		Cheryl	100
3	Bill	90		Giselle	100
4	Cheryl	100		Bill	90
5	Doug	85		Amy	85
6	Edie	75		Doug	85
7	Fred	85		Fred	85
8	Giselle	100		Edie	75
Sheet5

Cell Formulas
Range	Formula
AK2	=INDEX($AH:$AH,AGGREGATE(15,6,ROW($AH$2:$AH8)/($AI$2:$AI$8=AL2),COUNTIF($AL$2:$AL2,AL2)))
AL2	=LARGE($AI$2:$AI$8,ROWS($AL$2:$AL2))

 

[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.