In sheet one in column A I have a list of 2000 names (some of them repeated) and column B lists each person's score percentage. In sheet 2, in column A I list each person's name exactly once and in columns b, c, d I want to list their first, second, and third best percentage. I know I can use the LARGE function to get a best score from a list but I am not sure how to make it so the function only appilies to the individual listed in sheet 2 column A. Does that make sense?

Thanks for your help!

http://excelspreadsheetshelp.blogspot.com - http://scripting4v5.com

Look into how to use Pivot Tables.

You could also use array values to return you an array of matches to a lookup, then perform a LARGE on that.

http://www.get-digital-help.com/2009/10/25/how-to-...

The formula below is adapted from an article on Chop Pearson's site: http://www.cpearson.com/excel/ArrayFormulas.aspx and is a bit neater than given in LiteYear's link.

If the names are in B6:B105 the scores are in C6:C105 and a selected name is in E6 then:
(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE)
will return an array of 100 items containing either a score for the selected name or FALSE.
You can then wrap the Large() function around that:
=LARGE(IF($B$6:$B$105=$E6,$C$6:$C$105,FALSE),F$5)
where F5 contains the rank you want.

Note that because this is an array formula it must be entered by pressing "Ctrl-Shift-Enter". If you just press "enter" it returns 0.

A sample spreadsheet is attached.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

• http://files.engineering.com/getfile.aspx?folder=5fe35884-1279-402a-a5bb-5b

More details at http://newtonexcelbach.wordpress.com/2012/06/16/ex...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

Thanks for the help and the excellent write up!

ntweisen - Glad to help. I've added a bit more to the blog post in response to a comment, that may be of interest.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/