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

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
54
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,931
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 26, 2019
Messages
54
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
Is there a way to do it without using the aggregate function?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2019
Messages
54
Office Version
  1. 365
  2. 2011
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
Then:
=INDEX(SORTBY(B2:B7,O2:O7,-1),SEQUENCE(20))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,816
Messages
5,708,752
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top