Top n values lookup

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm after a formula that will return the corresponding name of person who has the maximum age. In this case Fred would go in A11 as he is 65 years of age. My formula albeit clunky does return the highest n values, this is determined by cell B10 and is dynamic, the problem is that I need it to also return the person.
Ultimately I would like to create a chart that only plots the n value, so if n = 5, the graph plots the highest 5 values and no more.

So I guess my question is can my formula be B11 onwards be condensed down (it might not be and that is ok), more importantly I need the name returned in A11 onwards. Hopefully from that, the chart should be ok, my main concern is that it won't pick up extraneous cells eg I ask for top 2 and it gives me the top 2 values as well as some empty cells.


Book10
ABC
1NameAgeSales
2Tom3339339
3Fred6510413
4Bert3065910
5Kate2936503
6Sara4217112
7Bill4037533
8Harry5952730
9
10Top 4
11#VALUE!65
12#VALUE!59
13#VALUE!42
14#VALUE!40
Sheet1
Cell Formulas
RangeFormula
A11:A14A11=IF(B11<>"",INDEX($B$2:$B$8,MATCH(B2,$B$2:$B$8,0),""))
B11:B14B11=IF(ROW()-ROW($11:$11)+1<=$B$10,LARGE($B$2:$B$8,ROW()-ROW($11:$11)+1),"")
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
If I'm understanding correctly, I'd just be going with =max(b2:b8) in cell B11, and =index(a2:a8,match($b$11,b2:b8,0)) in cell A11

For nth value in B12, B13 etc, I'd go with =large(b2:b8,2) and so on. The 2 is the 2nd largest. Set your A11 formula up properly with appropriate $ signs, then drag it down.
 
Upvote 0
You could use something like Ex. 1 below for the data you posted, but what happens if you have 2 people with the same age. Then you will need something like Ex. 2 below.

Ex. 1 with your data
Book1
ABC
1NameAgeSales
2Tom3339339
3Fred6510413
4Bert3065910
5Kate2936503
6Sara4217112
7Bill4037533
8Harry5952730
9
10Top 4
11Fred65
12Harry59
13Sara42
14Bill40
Sheet1
Cell Formulas
RangeFormula
A11:A14A11=IF(B11<>"",INDEX($A$2:$A$8,MATCH(B11,$B$2:$B$8,0)),"")
B11:B14B11=IF(ROWS($B$11:B11)>$B$10,"",LARGE($B$2:$B$8,ROWS($B$11:B11)))


Ex. 2 with duplicate ages.
Book1
ABC
1NameAgeSales
2Tom3339339
3Fred6510413
4Bert3065910
5Kate5936503
6Sara4217112
7Bill4037533
8Harry5952730
9
10Top 4
11Fred65
12Harry59
13Kate59
14Sara42
Sheet2
Cell Formulas
RangeFormula
A11:A14A11=INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/(B11=$B$2:$B$8),COUNTIF($B$11:B11,B11)))
B11:B14B11=IF(ROWS($B$11:B11)>$B$10,"",LARGE($B$2:$B$8,ROWS($B$11:B11)))
 
Upvote 0
Thanks Jaseair, just to clarify I'm looking for a formula that returns the nth highest values, n is dependent on what is required which is in B10
 
Upvote 0
Hi Ahoy,

Your formulae work for this situation and appreciate the duplicate case.
Do you/others know how I can now convert the output into a simple column chart, so if n=6, the chart actually only has 6 entries, or is it's 2 it only shows 2 values. My chart is picking up blank cells and trying to chart them.
 
Upvote 0
Maybe this link will help with your chart.

 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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