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.