Whenever you create a worksheet that holds information about a list of distinct items, such as products offered for sale by a company, you should ensure that at least one column in the list contains a unique value that distinguishes that row (and the item the row represents) from every other row in the list. Assigning each row a column with a unique value means that you can associate data in one worksheet with data in another worksheet. For example, if every customer is assigned a unique identification number, you can store a customer's contact information in one worksheet and all orders for that customer in another worksheet. You can then associate the customer's orders and contact information without writing the contact information in a worksheet every time the customer places an order.
In the case of shipments handled by Consolidated Messenger, the column that contains those unique values, or the primary key column, is the ShipmentID column.
If you know a shipment's ShipmentID, it's no trouble to look through a list of 20 or 30 items to find a particular shipment. If, however, you have a list of many thousands of shipments, looking through the list to find one would take quite a bit of time. Instead, you can use the VLOOKUP function to let your colleagues type a ShipmentID in a cell and have the corresponding details appear in another cell.
The VLOOKUP function finds a value in the leftmost column of a named range, such as a table, and then returns the value from the specified cell to the right of the cell with the found value. A properly formed VLOOKUP function has four arguments (data that is passed to the function), as shown in the following definition: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
The following table summarizes the values Excel 2007 expects for each of these arguments.
When range_lookup is left blank or set to TRUE, for VLOOKUP to work properly the rows in the named range specified in the table_array argument must be sorted in ascending order based on the values in the leftmost column of the named range.
The VLOOKUP function works a bit differently depending on whether the range_lookup argument is set to TRUE or FALSE. The following list summarizes how the function works based on the value of range_lookup:
As an example of a VLOOKUP function, consider the following worksheet and the accompanying VLOOKUP formula.
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. For more information on using the HLOOKUP function, click the Excel 2007 Help button, type HLOOKUP in the search terms box, and then click Search.
When you type CI02 in cell E3 and press , the VLOOKUP function searches the first column of the table, finds an exact match, and returns the value Northwind Traders to cell C3.
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 formula's result to display properly.
In this exercise, you create a VLOOKUP function to return the destination postal code of deliveries with ShipmentIDs typed in a specific cell.
USE the ShipmentLog workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Database folder.
BE SURE TO start Excel 2007 before beginning this exercise.
OPEN the ShipmentLog workbook.
CLOSE the ShipmentLog workbook.
CLOSE Excel 2007