How to find the best score based on condition

In this example, I have a workbook with two worksheets. On Sheet1, in column A, there is a list of more than 2000 individual’s names (some of them repeated) and column B lists each person's average test score percentage. In the second worksheet, called Sheet2, column A contains a list of each person's name exactly once and in columns b, c, d I want to list their first, second, and third best score or percentage.

How to use the LARGE function in Excel

The first, second, or third best score can be obtained using the Excel large function. The LARGE function allows you to return the nth largest value in a unique data set, like the second best score. The syntax for the large function excel is: =LARGE(array,k) where an array is a range of data and k is the position from the largest value in the array. So, for the first best score k=1, second best k=2, etc.

The LARGE function excel will get the best score from the list but we need to make it so the function only applies to the individual listed in sheet 2 column A. This is where the excel array function is used.

Using arrays in Excel

An array is a range of data, like items in a table. Often times you may see array formulas referred to as "CSE formulas," because you press CTRL+SHIFT+ENTER to transform the function into an excel array formula. We need to use our LARGE function in an array in order to return the best score based on the condition of the person’s name. So we’ll use the Excel large if.

To return the 2nd largest score for Joe, the name listed in Sheet2 column A, use this excel large formula:

{=LARGE(IF(Sheet1!$A$1:$A$2000=$A2,Sheet1!$B$1:$B$2000,-1),2)}

Remember, to enter this array formula into the cell, then instead of hitting the Enter key, hit Ctrl-Shift-Ente

Download the large and array examplespreadsheet from Google docs or join our email newsletter to receive it as an .xls attachment.