Microsoft Office Tutorials and References

In Depth Information

Returning the location of the maximum value in a range

The following array formula returns the row number of the maximum value in a single-column range named

Data
:

{=MIN(IF(Data=MAX(Data),ROW(Data), “”))}

The IF function creates a new array that corresponds to the
Data
range. If the corresponding cell contains the

maximum value in
Data
, the array contains the row number; otherwise, it contains an empty string. The MIN

function uses this new array as its second argument, and it returns the smallest value, which corresponds to the

row number of the maximum value in
Data
.

If the
Data
range contains more than one cell that has the maximum value, the row of the first maximum cell is

returned.

The following array formula is similar to the previous one, but it returns the actual cell address of the maximum

value in the
Data
range. It uses the ADDRESS function, which takes two arguments: a row number and a

column number.

{=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data), “”)),COLUMN(Data))}

The previous formulas work only with a single-column range. The following variation works with any sized

range and returns the address of the largest value in the range named
Data
:

{=ADDRESS(MIN(IF(Data=MAX(data),ROW(Data),

“”)),MIN(IF(Data=MAX(Data),COLUMN(Data), “”)))}

Finding the row of a value's nth occurrence in a range

The following array formula returns the row number within a single-column range named
Data
that contains the

n
th occurrence of the value in a cell named
Value
:

{=SMALL(IF(Data=Value,ROW(Data), “”),n)}

The IF function creates a new array that consists of the row number of values from the
Data
range that are equal

to
Value
. Values from the
Data
range that aren't equal to
Value
are replaced with an empty string. The SMALL

function works on this new array and returns the
n
th smallest row number.

The formula returns #NUM! if the value is not found or if
n
exceeds the number of occurrences of the value in

the range.

Returning the longest text in a range

The following array formula displays the text string in a range (named
Data
) that has the most characters. If

multiple cells contain the longest text string, the first cell is returned.

{=INDEX(Data,MATCH(MAX(LEN(Data)),LEN(Data),FALSE),1)}