Search code examples
excelsortingindexinglimit

Return Top 10 Values from a List (with Specific Conditions!)

I have a table containing a long list of names and information. I'd like to call up the ten names with the highest values of a certain parameter, in order of 1, 2, ... 10. I already ripped a formula from past internet searches that does exactly what I want I need, and it works great. This is it:

=INDEX(Salaries[Name],MATCH(1,INDEX((Salaries[Income]=LARGE(Salaries[Income],ROWS(C$3:C3)))*(COUNTIF(C$3:C3,Salaries[Name])=0),),0))

This formula is copied from C4 and pasted down through C13.

I then use =VLOOKUP($C4,Salaries,COLUMNS(Salaries[[#Headers],[Name]:[Age]]),FALSE) and =VLOOKUP($C4,Salaries,COLUMNS(Salaries[[#Headers],[Name]:[Income]]),FALSE) to bring bring up the corresponding ages and incomes in columns D and E, respectively.

Screenshot

Now, I'm getting hung up on this last part. I'd like to have a user-defined age limit that will control the "Top 10" results. (Example: The user defines a maximum age of 35 and the list is populated with the names of the people with the top ten highest incomes and are also 35 years old and under.)

I know this can easily be done on the table itself by simply sorting the income column and setting a filter on the age column, but I want to have this ten-person list to be formula driven. Does anyone have any recommended edits to my existing formula or a completely different way of going about this?

File Download Link

Solution

  • So Change your first Equation to this:

     =INDEX(Salaries[Name],MATCH(1,INDEX((Salaries[Income]=LARGE(IF(Salaries[Age]<= $A$3,Salaries[Income]),ROWS(C$3:C3)))*(COUNTIF(C$3:C3,Salaries[Name])=0),),0))

    This is an array formula so confirm with Ctrl-Shift-Enter