A blogger was wondering, whether it was possible to retrieve a reference to the cell where the result of the functions MAX or LARGE was found. With the proper combination of INDEX and MATCH, this is indeed possible.

Basically, the idea is to use the result of the MAX (or MIN or LARGE function) as an input for the standard INDEX-MATCH formula.

Suppose the range containing your data is called *data_range.*

Then,

*third_largest* = LARGE(data_range,3) returns the 3rd largest value in *data_range*.

*p =* MATCH(third_largest ,data_range,0) returns the relative position within *data_range *

The following returns a reference to the cell placed on the *p-th* row of the provided data range:

=INDEX(data_range,*p*)

If your data was arranged horizontally, you would use

=INDEX(data_range,1,*p*)

which tells excel to look at the first row and *p-th* column of the data range.

Put together, if your data is arranged in a column, this gives:

=INDEX(data_range,MATCH(LARGE(data_range,3),data_range,0))

If you enter this formula on a spreadsheet, it will look like it returns the same value as the simpler* LARGE(data_range,3)* formula. This only appears to be so. When applied to a range, the INDEX function really returns a reference to a cell.

Practically, this means you can nest this formula in functions that take ranges as arguments. Like OFFSET, ROW, COLUMN, …

For example,

=ROW(INDEX(data_range,MATCH(LARGE(data_range,3),data_range,0)))

returns the absolute row number at which the first occurrence of the value was found.

This technique has its limits of course: it only allows you to retrieve the first cell which meets the given value.

Is it possible to retrieve all values for which a MAX value is reached?

Yes, but it quickly becomes more complex:

= LARGE(

IF(data_range=MAX(data_range),ROW(data_range)),

ROW(

OFFSET($A$1,0,0,

COUNT(

IF(data_range=MAX(data_range),ROW(data_range))

)

)

)

)

This formula, when entered as an array formula, will return all absolute row numbers of the cells that contain the max value.

The IF part checks whether the cells in *data_range* contain the max value. If this is the case, it will return the absolute row number for the range, else it will return the value FALSE. The ROW-OFFSET part is a trick to dynamically build an array containing the values 1 to N, where *N* stands for the number of cells containing the max value The LARGE part, in our example, will return an array with the relevant values. Note the large function ignores boolean values (TRUE/FALSE).

Let me note, at this point, that before you write such a formula in your worksheets, you may want to check first whether no other option is available (writing intermediate formulas, UDF in VBA, pivot table, …)

April 29, 2012 at 2:10 pm

Wow! I have used =LARGE to find targets for =MATCH, but didn’t think to use it to “automagically” iterate to the next possible value!

In the end, my effort was a kludge; I manually edited the cell ref’s (as I had no idea far apart the ref’s of matching indices lay) of each row containing my magic formula (see http://ncsizer.wordpress.com/2012/03/07/excel-shenanigans/), while editing

a copied version to the adjacent column (that also looks up adjacent data!)againI think a follow-up post would be a better idea!