Microsoft Office Tutorials and References

In Depth Information

Tip
The related
HLOOKUP
function matches a value in a column of the first row of a table

and returns the value in the specified row number of the same column. The letter “H” in

the
HLOOKUP
function name refers to the horizontal layout of the data, just as the “V” in the

VLOOKUP
function name refers to the data’s vertical layout. For more information on using

the
HLOOKUP
function, click the Excel Help button, type
HLOOKUP
in the search terms box,

and then click Search.

Important
Be sure to give the cell in which you type the
VLOOKUP
formula the same format

as the data you want the formula to display. For example, if you create a
VLOOKUP
formula

in cell G14 that finds a date, you must apply a date cell format to cell G14 for the result of the

formula to display properly.

In this exercise, you’ll create a
VLOOKUP
function to return the destination postal code

of deliveries with ShipmentIDs typed in a specific cell.

SET UP
You need the ShipmentLog_start workbook located in your Chapter06 practice

file folder to complete this exercise. Open the ShipmentLog_start workbook, and save

it as
ShipmentLog
. Then follow the steps.

1.
In cell
C3
, type the formula
=VLOOKUP(B3, Shipments, 5, FALSE)
.

Cell B3, which the formula uses to look up values in the Shipments table, is blank,

so the
#N/A
error code appears in cell C3.

2.
In cell
B3
, type
SI3049224
, and press Enter.

The value
51102
appears in cell C3.

3.
In cell
C3
, edit the formula so that it reads
=VLOOKUP(B3, Shipments, 2, FALSE)
.

The formula now finds its target value in table column 2 (the CustomerID column),

so the value
CI512191
appears in cell C3.

4.
In cell
C3
, edit the formula so that it reads
=VLOOKUP(B3, Shipments, 4, TRUE)
.

Changing the last argument to
TRUE
enables the
VLOOKUP
formula to find an

approximate match for the ShipmentID in cell B3, whereas changing the column to
4
means

the formula gets its result from the OriginationPostalCode column. The value
14020

appears in cell C3.