Page 90 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

DETERMINE THE LOCATION OF A VALUE

o determine the location of a specific value within a The optional match_type argument defines how Excel
row or column of a worksheet, you can use the compares the value from the lookup_value argument to
T MATCH worksheet function. This function is the range of cell values if it cannot find an exact match. If you
important when your data analysis requires you to know omit the argument, or if the argument contains a value of 1,
the location of a specific value, or its closest match. Excel finds the largest value that is less than or equal to the
lookup_value. For Excel to find the closest match, you
The MATCH function syntax includes three arguments, of must first sort the list of cell values in ascending order. If the
which the first two are required. The last is optional. value of the match_type argument is 0, Excel must find an
exact match. If the value is -1, Excel returns the smallest value
=MATCH(lookup_value, lookup_array, that is greater than or equal to the lookup_value. With a
[match_type]) value of -1, you must sort the list of cell values in descending
order. See Chapter 2 for more on sorting your data.
The lookup_value argument defines the value you want
to locate within the specified range of cells. You can specify When you use the MATCH function, it returns an integer
a number, text, logical value, or a cell reference containing value that identifies the location of the value within the
the value you want to locate. The lookup_array defines a specified range of cells. For example, if Excel returns the
range of contiguous cells, referred to as an array, in a row or value 2 and the specified range of cells in A4 through A24,
column to search. cell A5 contains the value, or the closest match.

DETERMINE THE LOCATION OF A VALUE

MATCH

⁄ Open a worksheet that Note: See Chapter 2 for more ■ The Insert Function dialog Note: See the section "Add a
contains the data list you information on sorting your data. box displays. Function via the Insert Function
want to search. Dialog Box" for more information
¤ Select the formula cell. › Select the MATCH on this dialog box.
■ To find the closest match, function.
sort the list in ascending or ‹ Click Insert ➪ Function. ˇ Click OK.
descending order.

76