Microsoft Office Tutorials and References
In Depth Information
row_num — The number of the row in referencefrom which to return a
column_num — The number of the column in referencefrom which to re-
turn a reference.
area_num — Selects a range in referencefrom which to return the in-
tersection of row_numand column_num. The first area selected or
entered is numbered 1, the second is 2, and so on. If area_numis omitted,
INDEX uses area 1. For example, if referencedescribes the cells
(A1:B4,D1:E4,G1:H4), then area_num1 is the range A1:B4, area_num2 is
the range D1:E4, and area_num3 is the range G1:H4.
After referenceand area_numhave selected a particular range, row_numand
column_numselect a particular cell: row_num1 is the first row in the range,
column_num1 is the first column, and so on. The reference returned by INDEX
is the intersection of row_numand column_num.
If you set row_numor column_numto 0, INDEX returns the reference for the
entire column or row, respectively.
row_num, column_num, and area_nummust point to a cell within reference;
otherwise, INDEX returns a #REF! error. If row_numand column_numare omit-
ted, INDEX returns the area in referencespecified by area_num.
The result of the INDEX function is a reference, and it is interpreted as
such by other formulas. Depending on the formula, the return value of INDEX
may be used as a reference or as a value. For example, the formula
CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The
CELL function uses the return value of INDEX as a cell reference. On the
other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return
value of INDEX into the number in cell B1.
Using this version of INDEX, you can build formulas that work on one par-
ticular area in a named range. Here ’ s how you do it:
11. In B15:E15, enter the numbers 1 through 4. These correspond to the four
areas in MyAreas.
22. When you build the INDEX function, you want Excel to return a refer-
ence to all the rows and columns of the first area of the range, so use
=INDEX(MyAreas,,,1) to return such a reference.
33. Instead of using 1 for the areasargument of INDEX, use