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