Microsoft Office Tutorials and References

In Depth Information

**Looking up information in a worksheet**

As an example of a
VLOOKUP
function, consider an Excel table that has its headers in

row 1 and the first column in column A of the worksheet. If the
=VLOOKUP (D2, Table1,

2, TRUE)
formula is used, when you enter
CI02
in cell D2 and press Enter, the
VLOOKUP

function searches the first column of the table, finds an exact match, and returns the value

Northwind Traders
to cell E2.

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, enter
HLOOKUP
in the search terms

box, and then click Search.

6

IMPORTANT
Be sure to format the cell in which you enter the
VLOOKUP
formula with 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 that have ShipmentIDs entered in a specific cell.

SET UP
You need the ShipmentLog workbook located in the Chapter06 practice file

folder to complete this exercise. Open the workbook, and then follow the steps.

1

In cell
C3
, enter 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
, enter
SI3049224
, and press
Enter
. The value
51102
appears in cell
C3
.