Microsoft Office Tutorials and References

In Depth Information

**Syntax**

•
row_num

row_num
—
The number of the row in referencefrom which to return a

reference.

•
column_num

column_num
—
The number of the column in referencefrom which to re-

turn a reference.

•
area_num

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

=INDEX(MyAreas,,,B$15).