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
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