Microsoft Office Tutorials and References

In Depth Information

**Understanding lookup and reference functions**

the value or values at that cell address. Remember that the result is an address, even if it

doesn’t look like one.

Here are a few guidelines to keep in mind when using the INDEX function:

If you type
0
as the
row_num
or
column_num
argument, INDEX returns a reference

for the entire row or column, respectively.

●

The
reference
argument can be one or more ranges, which are called
areas
. Each area

must be rectangular and can contain numbers, text, or formulas. If the areas are not

adjacent, you must enclose the
reference
argument in parentheses.

●

You need the
area_num
argument only if you include more than one area in

reference
. The
area_num
argument identifies the area to which the
row_num
and
column_

num
arguments will be applied. The first area specified in
reference
is designated
area

1
, the second
area 2
, and so on.

●

Let’s consider some examples to see how all this works. Figure 14-5 shows an example of an

INDEX function. The formula in cell A1 uses the row coordinate in cell A2 and the column

coordinate in cell A3 to return the contents of the cell in the third row and second column

of the specified range.

Figure 14-5
Use the INDEX function to retrieve the address or value in a cell where information

is located.

The following example is a bit trickier: Using the same worksheet as in Figure 14-5, the

formula =INDEX(C3:E6, 0, 2) displays the #VALUE! error value because the
row_num

argument of 0 returns a reference to the entire column specified by the
column_num
argument

of
2
, or the range D3:D6. Excel can’t display a range as the result. However, try nesting this

formula in another function as follows: =SUM(INDEX(C3:E6, 0, 2)). The result is
2600
, the

sum of the values in D3:D6. This illustrates the utility of obtaining a reference as a result.

Now we’ll show how the INDEX function works with multiple ranges in the
reference

argument. (When you’re using more than one range, you must enclose the argument in