Returning Top Values

Posted by James on March 02, 2001 7:54 AM

I am still fairly new to Excel and have an interesting question that I hope someone can answer.

Lets say I have a spread sheet containing three columns. Col1 is for Names and Col2 and 3 are for numerical data. Is it possible that on another page that I can display the top 5 values in Col3 as well as the name in Col1 associated with it?

It is sort of like the sort feature except I do not want to sort the data on page 1 and on page 2 I only want the top 5.

Can anyone help with this?

Thank you very much,
James

Posted by cpod on March 02, 2001 9:49 AM

You can use the Large() function to return the top 5 values in your list.

To return the name you will have to use the match() and index() functions. If you have the top five values in G2:G6 then:

=INDEX($A$2:$A$16,MATCH(G2,$C$2:$C$16,0),1)

Posted by James on March 02, 2001 11:24 AM

Thanks that worked great. Can I add one more twist to it:

Instead of having 1 continous display cover multiple pages, I would like to split the data in half so that it all displays on one screen. i.e

A.....b.....c.....d.....e.....f.....g.....
name val1 val2 name val1 val2

Can you apply the index/match function covering cols a and e with cols c and g?

Thanks greatly,
James

Posted by James on March 02, 2001 11:47 AM

I actually found a minor (or large considering the application) problem with this. If there are three people with the value of 4, then only the first person is displayed for all three.

Thanks again for your help