# Using MATCH and MAX to find largest, 2nd largest, 3rd largest, etc. in column

#### Dustinkli

##### Board Regular
I am referencing my sheet called WatchList and I am looking at the values in column O to find the largest, 2nd largest, 3rd largest, etc. values in that column and returning the corresponding cell in column B.

So if the highest value in column O in the sheet called WatchList is 54, and the corresponding cell in column B is "Pepsi" it will return Pepsi.
Excel Formula:
``=INDEX(WatchList!B:B,MATCH(MAX(WatchList!O:O,1),WatchList!O:O,0))``

This code I use to bring back the 2nd largest:
Excel Formula:
``=INDEX(WatchList!B:B,MATCH(MAX(WatchList!O:O,1),WatchList!O:O,0))``

This works well, except for when the cells in column B have the same numeric value in column O. If they have the same numeric value, for some reason it brigs back only the highest value again.

For instance if "Coke" also has a value of 54 then the 2nd formula above won't bring back "Coke" it will still bring back "Pepsi" for some reason.

I'm not sure why or how to modify the code to fix this issue.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

##### Well-known Member
Hi Dustinkli,

I'd need to store the maximums in a work column, if that helps:

Dustinkli.xlsx
BCDMNOPQR
1DrinkValueMaximumDrink
2Pepsi54999Milk
3Coke5499Beer
4Water3388Cider
5Cider8854Pepsi
6Beer9954Coke
7Milk99933Water
Watchlist
Cell Formulas
RangeFormula
Q2:Q7Q2=AGGREGATE(14,6,O:O,ROW()-ROW(\$Q\$1))
R2:R7R2=INDEX(B:B,AGGREGATE(15,6,ROW(B:B)/(O:O=Q2),COUNTIF(\$Q\$1:\$Q2,Q2)))

#### Dustinkli

##### Board Regular
Is there a way to do it without using the aggregate function?

#### Fluff

##### MrExcel MVP, Moderator
As you have 365 how about
+Fluff 1.xlsm
BNOPQ
1DrinkValueDrink
2Pepsi54Milk
3Coke54Beer
4Water33Cider
5Cider88Pepsi
6Beer99Coke
7Milk999Water
8
Lists
Cell Formulas
RangeFormula
Q2:Q7Q2=SORTBY(B2:B7,O2:O7,-1)
Dynamic array formulas.

#### Dustinkli

##### Board Regular

As you have 365 how about

That works, but it returns all of the cells and in my situation I have about 1,500 rows. Is there a way to make it just return the top 20 or so?

#### Habtest

##### Board Regular
Then:
=INDEX(SORTBY(B2:B7,O2:O7,-1),SEQUENCE(20))

#### Fluff

##### MrExcel MVP, Moderator
Another option, if you don't want to hard code the number of results to show
+Fluff 1.xlsm
ABNOPQR
1DrinkValueDrink5
2Pepsi54Milk
3Coke54Beer
4Water33Cider
5Cider88Pepsi
6Beer99Coke
7Milk999
8
Lists
Cell Formulas
RangeFormula
Q2:Q6Q2=INDEX(SORTBY(B2:B7,O2:O7,-1),SEQUENCE(R1))
Dynamic array formulas.

Replies
10
Views
295
Replies
4
Views
212
Replies
7
Views
114
Replies
5
Views
891
Replies
2
Views
141

1,141,814
Messages
5,708,732
Members
421,588
Latest member
Wawie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back