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.

How do I create a two variable lookup in Excel Spreadsheet?

One way to create a two variable lookup formula in Excel 2007 is to use the INDEX and MATCH functions.

First, the INDEX function returns either the value or the reference to a value from a table or range. The syntax for the INDEX function is:

Index( array, row_number, column_number ) where array is a range of cells or table, row_number is the row number in the array to use to return the value, and column_number is the column number in the array to use to return the value.

The MATCH function searches for a value in an array and returns the relative position of that item. The syntax for the Match function is:

Match( value, array, match_type ) where value is the value to search for in the array, array is a range of cells that contains the value that you are searching for, and match_type is optional.

Putting it all together:

=INDEX(LookUp!$AA$2:$AA$156,MATCH($C2&$D2,LookUp!$X$2:$X$156&LookUp!$Y$2:$Y$156,0))

An important note is you MUST enter it via ctrl+shift+enter, not just enter. You'll know you did it correctly when it creates an extra set of special brackets around your formula ({}).

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula.