Formulas and Functions

INDEX
The INDEX function returns the value in the cell located at the intersection of the
specied row and column within a range of cells or an array.
INDEX(range, row-index, column-index, area-index)
 range: A range of cells. range may contain values of any type. range is either a single
range or more than one range separated by commas and enclosed in an additional
set of parentheses. For example, ((B1:B5, C10:C12)).
 row-index: The row number of the value to be returned. row-index is a number
value and must be greater than or equal to 0 and less than or equal to the number
of rows in range.
 column-index: An optional value specifying the column number of the value to be
returned. column-index is a number value and must be greater than or equal to 0
and less than or equal to the number of columns in range.
 area-index: An optional value specifying the area number of the value to be
returned. area-index is a number value and must be greater than or equal to 1 and
less than or equal to the number of areas in range. If omitted, 1 is used.
Usage Notes
INDEX can return the value at the specied intersection of a two-dimensional range Â
of values. For example, assume that cells B2:E7 contain the values. =INDEX(B2:D7, 2,
3) returns the value found at the intersection of the second row and third column
(the value in cell D3).
More than one area can be specied by enclosing the ranges in an additional pair Â
of parentheses. For example, =INDEX((B2:D5,B7:D10), 2, 3, 2) returns the value at the
intersection of the second column and the third row in the second area (the value in
cell D8).
INDEX can return a one-row or one-column array for another function. In this Â
form, either row-index or column-index is required, but the other argument may be
omitted. For example =SUM(INDEX(B2:D5, , 3)) returns the sum of the values in the
third column (cells D2 through D5). Similarly, =AVERAGE(INDEX(B2:D5, 2)) returns the
average of the values in the second row (cells B3 through D3).
INDEX can return (or “read”) the value from an array returned by an array Â
function (a function that returns an array of values, rather than a single value).
The FREQUENCY function returns an array of values, based on specied intervals.
=INDEX(FREQUENCY($A$1:$F$5, $B$8:$E$8), 1) would turn the rst value in the array
returned by the given FREQUENCY function. Similarly =INDEX(FREQUENCY($A$1:$F$5,
$B$8:$E$8), 5) would return the fth value in the array.
The location in the range or array is specied by indicating the number of rows Â
down and the number of columns to the right in relation to the cell in the upper-
left corner of the range or array.
214 Chapter 9 Reference Functions