Top n values lookup

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
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),"")
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016
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.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
491
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
Maybe this link will help with your chart.

 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,020
Members
415,946
Latest member
bellerom

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