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

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
62
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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)))
 
Upvote 0
Is there a way to do it without using the aggregate function?
 
Upvote 0
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.
 
Upvote 0
Then:
=INDEX(SORTBY(B2:B7,O2:O7,-1),SEQUENCE(20))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top