# Top n values lookup

#### Tigerexcel

##### Active Member
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

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
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
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.

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
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
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.

Replies
16
Views
363
Replies
5
Views
121
Replies
1
Views
75
Replies
3
Views
157
Replies
1
Views
194

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.

### 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