Microsoft Office Tutorials and References
In Depth Information
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 calcu-
lates 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.
Figure 15-5 shows an example. This function works only if the Data range consists of a single column.
FIGURE 15-5: Using an array formula to return the longest text in a range.
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 ). Fig-
ure 15-6 shows an example.
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.
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 .