|Using Lookup Functions in Microsoft Excel 2000
Tables are wonderful. If it weren’t for tables, everything would be in one long list. You’d know where all your data could be found, but you still couldn’t find it. Location’s not everything — organization is.
We use tables mainly so we can quickly find the information we need. We also use tables for organizing, storing, and maintaining data. This saves us the trouble of having to remember everything — we can just look it up.
Spreadsheets, including Microsoft Excel, have some of the same needs for data storage and retrieval that humans do. It is inconvenient to have to cut, copy, and paste information such as product names, prices, and descriptions into a spreadsheet by hand — or to have to type it in. Manual data entry processes are the cause of many errors in spreadsheets.
Wouldn’t it be great if the spreadsheet itself could go look up the information and copy it to the right place?
As you have probably guessed by now, that’s exactly what Excel 2000 can do, using its “lookup” functions. You can place a “lookup table” on a worksheet and Excel can retrieve data from it from a location on the same worksheet, or in the same workbook, or even from another workbook.
This means, for example, that lookup functions on an invoice worksheet can handle the details of putting the unit price in the right place. When prices change, they only need to be changed in the lookup table, not in every individual invoice that is written.The Lookup Functions
Excel provides two functions whose only purpose is to be able to pluck the desired data from a lookup table for use somewhere else. They work pretty much as you do when you look up information in a table.
The more commonly used of these functions is VLOOKUP, or “Vertical Lookup.” It searches a designated area of a worksheet for specific information, and it does it column by column.
When you look in a table, you generally will look down the leftmost column until you find the item for which you need information. Then you look to the right to the column with the heading indicating the detail you require.
If you think of a spreadsheet as a flat-file database, then a row is a record and the columns are each a field within the record. This is how VLOOKUP approaches data retrieval. It looks for a specific record in the leftmost column (the first field). When it finds it, it retrieves the information in a particular field. Use VLOOKUP when the first (leftmost) column of the lookup table contains the “name” of the thing you are looking up, and the data you want about it is in one of the columns to the right of the Data column.
The other lookup function is HLOOKUP. This gets used less, in part because it looks for a column heading first, then goes to a specific row and retrieves whatever it finds in that cell. This is a horizontal lookup approach. This “across and down” approach is contrary to the way we humans generally search. Use HLOOKUP when the “name” of the thing you are looking for is in the first row of the lookup table and the actual data is in one of the rows below it.
The two functions are very similar. Each one uses four arguments:
This is the table or the array of values containing the data set that the lookup functions will search for a value to return to the spreadsheet cell needing the information. If the worksheets were networked computers, the lookup table would be the server, and the cell where the information goes would be the client.
The information that identifies the “name” goes either in the leftmost column or in the topmost row (see Figure 1). In the former case, you would use VLOOKUP, in the latter, HLOOKUP.
Figure 1: A table array arranged for vertical lookup (VLOOKUP)
Usually the Lookup Table is the first element created and populated with data. It is best to name the range(s) to be searched, although it is perfectly permissible to use standard cell references to identify the Lookup Table.
It is also a good practice to arrange the values in the first column in ascending order, unless you know that you will always want to have an exact match — that is, unless range_lookup will ALWAYS be FALSE. If range_lookup is TRUE and the first column values are not in ascending order, the lookup function may return the incorrect value.The VLOOKUP Function
The format for VLOOKUP is as follows:
In the example shown in Figure 2, entering the labor hours for each element of the project causes VLOOKUP to go to the lookup table (Figure 1) for the hourly rate for the element, and to enter it in the proper column. The lookup table in this case is on a different worksheet in the same workbook.
Figure 2: A project worksheet that computes all the labor costs for the manager
The function entered in this case was =VLOOKUP(A10,labor_rates,2). When an element is entered in column A, VLOOKUP automatically obtains the hourly rate from the second column of the lookup table. Since the lookup_value was entered as a relative reference, the formula updates when copied to the next row.The HLOOKUP Function
The format for HLOOKUP is:
Usage is exactly the same as for VLOOKUP (see Figure 3). In this case, the function is =HLOOKUP(“Hotel”,per_diem,2). Notice that “Hotel” must be in double quotes, since it is a string. The table updates as soon as the number of days is entered.
Figure 3: A project worksheet that retrieves and enters travel allowances
So now, when someone tells you to look something up, you can put Excel on the job instead!