This post is in response to a query at the Eng-tips forum asking how to extract selected data from a range, so that the three best scores for each individual could be extracted from a two column Excel table listing multiple individuals, each with several different scores.
My response was based on an article by Chip Pearson at: http://www.cpearson.com/excel/ArrayFormulas.aspx
If the names are in B6:B105 the scores are in C6:C105 and a selected name is in E6 then:
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:
where F5 contains the rank you want. An example is shown below, listing the three best scores of Thomas, Richard and Harold:
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.
The working of the formula is shown below, with the results of the formula:
Note that the array includes one entry for every row of the original table, containing either a score for Thomas, or FALSE if the row is data for another person. This is not a problem for use with the Large() function, but if a continuous list of scores for reach individual is required then further work is required.
In many cases simply sorting the table, using the Excel filter functions, or using a pivot table would be the easiest way to go, but if an automatically updating array formula is required it is possible to wrap some additional functions around the solution presented above, such as that shown at The Get Digital Help blog (modified version shown below):
will return an array of row numbers for rows containing scores for Thomas, with FALSE in the other rows.
Extracts the row numbers for Thomas from this array, without the FALSE values, and:
extracts the actual score from those row numbers.
In this case the first approach is not only much shorter, it is also much more convenient because before the Large() function can be used on the array returned by the second method the length of the array needs to be determined.
Once again, remember all the formulas presented here must be entered as array formulas. See Using Array Formulas for more details. Failure to enter as an array will sometimes result in an error message, but can return incorrect values, so beware.
Edit 19th June 12: The comment from oli below is worth a closer look. The basic technique is to replace the IF statement with an array multiplication:
($B$5:$B$104=E5) returns an array of TRUE and FALSE values. When these are multiplied by the values in C5:C104 it returns an array of the values in column C or 0 (since TRUE and FALSE are treated as equal to 1 and 0 in numerical calculations), and then the Large function may be used directly on that array. This is a very powerful technique, with many applications, the only proviso being that it is not suitable where the data may include 0 values, since it will be impossible to differentiate between this value and the values that do not match the selection criteria.
The other difference is that the Large function has also been applied as an array formula, so that rather than array entering the formula in a single cell and copying it to the two adjacent cells it must be array entered with the three adjacent cells selected. It will then return the top three scores for the chosen person.