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.
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:
If your data was arranged horizontally, you would use
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:
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, …
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:
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, …)