Microsoft Office Tutorials and References
In Depth Information
Using Excel Functions
A typical lookup table.
One important factor with VLOOKUP (and
HLOOKUP) is that the first column (or first
row) MUST be in alphabetical order. See
Chapter 11, “Managing Large Amounts of
Data,” for instructions on sorting your data.
The steps in the previous section used the
VLOOKUP function because the data resided in
columns. In an HLOOKUP the data resides in rows.
HLOOKUP works just like the VLOOKUP except it
looks in the top row of the list and returns the
value of the indicated cell.
The second argument table_array is prompting you
to specify where you have your list. Let’s call it list
area . You can enter a range name or you can
specify the range of cells. Do not include column
headings. In this example, the list is in cells A2 through
C24, which you would specify as A2:C24.
The HLOOKUP syntax is HLOOKUP( lookup_value,
table_array,row_index_num ) or as translated earlier,
=HLOOKUP( lookup cell, list area, row number ). Figure
9-19 illustrates the HLOOKUP function in action.
The third argument asks which column of the list
do you want to extract. Again referring to the
example, since you want the number of calls, you
need the second column.
So after translating the VLOOKUP to read
=VLOOKUP( lookup cell, list area, column number ),
you need to enter =VLOOKUP(E3,A2:C31,2), which
gives you a result of 6,776. If you type another city
in cell E3—Boston, for example—you get a different
result of 9,492. You can verify those results by
manually looking up Chicago or Boston in the list
(see Figure 9-18).