Tigerexcel
Active Member
 Joined
 Mar 6, 2020
 Messages
 491
 Office Version

 365
 2019
 Platform

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

A  B  C  
1  Name  Age  Sales  
2  Tom  33  39339  
3  Fred  65  10413  
4  Bert  30  65910  
5  Kate  29  36503  
6  Sara  42  17112  
7  Bill  40  37533  
8  Harry  59  52730  
9  
10  Top  4  
11  #VALUE!  65  
12  #VALUE!  59  
13  #VALUE!  42  
14  #VALUE!  40  
Sheet1 
Cell Formulas  

Range  Formula  
A11:A14  A11  =IF(B11<>"",INDEX($B$2:$B$8,MATCH(B2,$B$2:$B$8,0),"")) 
B11:B14  B11  =IF(ROW()ROW($11:$11)+1<=$B$10,LARGE($B$2:$B$8,ROW()ROW($11:$11)+1),"") 