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 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:

• Lookup_value identifies the value to be looked up; this can be a value, a text string, or a cell reference.
  
  
• The table_array (lookup table) in which the lookup value is stored; this is a range of cells, identified either in the usual “A1:B2” format, or given a range name (named ranges are preferred).
  
  
• An index number to identify the column (VLOOKUP) or the row (HLOOKUP) from which the value will come. These arguments are col_index_num and row_index_num, respectively. Only the one appropriate to the function and the table configuration will be used.
  
  
• A range lookup logical value (range_lookup) to specify either an exact match or an approximate match. If TRUE or omitted, the function looks for an approximate match (the largest value that is less than the lookup_value). If FALSE, the function looks for an exact match with lookup_value.

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.

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.

=VLOOKUP(lookup_value,table_array,col_index_num, range_lookup)

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.

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.

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

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.

So now, when someone tells you to look something up, you can put Excel on the job instead!