Page 92 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 78

EXCEL DATA ANALYSIS

RETURN A VALUE AT A SPECIFIC

LOCATION IN A DATA LIST

f you have a data list sorted in a particular order, such as The array argument denotes the range of cells. The
daily sales totals, you may want to find a particular value row_num argument is an integer value of the row number
Ibased upon its location in the list. To determine the value within the range that the array argument specifies. The
of a specific cell within a data list, you can use Excel's INDEX column_num argument is an integer value specifying the
workbook function. For example, you can find the value column number, and it is optional.
within the second row and third column of a particular range, To work with multiple cell ranges that are not contingous,
and have Excel place the value in the cell that you specify. If such as A1:B5 and D1:E5, you use the reference version of
your range of cells is A2 though D10, the INDEX function the INDEX function, which has the following syntax:
returns the value in cell B3 because that is the cell in the
second row and third column of the specified range of cells. =INDEX(reference, row_num, [column_num],
[area_num])
The INDEX function has two different formats: the array form
and the reference form. You use the array form of the INDEX You use the reference argument to define one or more
function when you want to locate values from an array, or non-contiguous cell ranges. To specify multiple cell ranges,
contiguous block of cells. For example, you would consider you enclose each cell range within parentheses. The optional
the range A1:C10 an array of cells because it is one continuous area_num argument defines an integer value, and is the cell
block. The array form of the function has the following syntax: range where you want to find the value. For example, if the
value of area_num is 2, Excel finds the value in the second
=INDEX(array, row_num, [column_num]) range at the designated row and column. Both the
column_num and area_num arguments are optional.

RETURN A VALUE AT A SPECIFIC LOCATION IN A DATA LIST

INDEX

⁄ Open a worksheet that ¤ Select the formula cell. ■ The Insert Function dialog Note: See the section "Add a
contains the data list you box displays. Function via the Insert Function
want to search. ‹ Click Insert ➪ Function. Dialog Box" for more information.
› Select the INDEX
function. ˇ Click OK.
■ The Function Arguments
dialog box displays.
78