Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

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
nth 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)}

This formula works with two arrays, both of which contain the length of each item in the Data

range. The MAX function determines the largest value, which corresponds to the longest text

item. The MATCH function calculates the offset of the cell that contains the maximum length. The

INDEX function returns the contents of the cell containing the most characters. This function

works only if the
Data
range consists of a single column.

Determining whether a range contains valid values

You may have a list of items that you need to check against another list. For example, you may

import a list of part numbers into a range named
MyList
, and you want to ensure that all the part

numbers are valid. You can do so by comparing the items in the imported list to the items in a

master list of part numbers (named
Master
).

The following array formula returns TRUE if every item in the range named
MyList
is found in the

range named
Master
. Both ranges must consist of a single column, but they don’t need to contain

the same number of rows.

{=ISNA(MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

The array formula that follows returns the number of invalid items. In other words, it returns the

number of items in
MyList
that do not appear in
Master
.

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

To return the first invalid item in
MyList
, use the following array formula:

{=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}