Page 88 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

LOOK UP A VALUE IN A SPECIFIC

ROW AND COLUMN

f part of your data analysis entails locating a specific The lookup_value argument identifies the column heading
value within a row or column of a data list, you should for the desired column. You can make the value text, a
I consider using Excel's lookup worksheet functions. Excel number, or even a logical value. Excel does not differentiate
provides two functions you can use to locate a value in between upper and lowercase text. The table_array
either a specific row or column: HLOOKUP and VLOOKUP. argument identifies the range of cells containing the data list.
The function you select is based upon whether your data Excel uses the first row in the range as the heading row. The
list contains column headings in the first row, or row row_index_num argument specifies the number of the row
headings in the first column. within the data list to return. If the value of the argument is 2,
Excel returns the second row of the data list, not the second
You use the HLOOKUP function when you have column row in the worksheet. The optional range_lookup
headings in the first row of the data list. Excel locates the argument lets you define whether you want an exact match
specified column, based upon the column heading or the closest match. If the value is False, Excel only returns
specified, and then returns the value in the specified row. a value if it finds an exact match to the value that the
The HLOOKUP function has four arguments of which Excel lookup_table argument specifies.
requires the first three arguments. The last is optional.
Although the following example illustrates the use of the
HLOOKUP(lookup_value, table_array, HLOOKUP function, VLOOKUP works the same, with the
row_index_num, [range_lookup]) exception that you make selections based upon a row
heading in the first column of the data list.
LOOK UP A VALUE IN A SPECIFIC ROW AND COLUMN

HLOOKUP

⁄ Open a worksheet that ‹ Click Insert ➪ Function. › Select the HLOOKUP Note: See the section "Add a
contains the data list you function. Function via the Insert Function
want to search. ■ The Insert Function dialog Dialog Box" for more information.
box displays. ■ To locate a value based
¤ Select the formula cell. upon the row heading, select ˇ Click OK.
the VLOOKUP function.
■ The Function Arguments
dialog box displays.

74