Section 12.1. The Basic Lookup | Excel 2007[c] The Missing Manual

12.1. The Basic Lookup

VLOOKUP( ) and HLOOKUP( ) are the two most popular lookup functions. They perform the same taskfinding and copying databut they look in different directions.

VLOOKUP( ) is the vertical lookup function . You use it to find a specific row in a large table of data. VLOOKUP( ) works by scanning the values in a single column from top to bottom. Once it finds the entry you're looking for, it can then retrieve other information from the same row. VLOOKUP( ), examined in closer detail in the next section, is the more commonly used of the two lookup functions.
HLOOKUP( ) is the horizontal lookup function . You use it to find a specific column in a large table of data. HLOOKUP( ) works by scanning the values in a single row from left to right. Once it finds the entry you're looking for, it can then retrieve other information from the same column.

Both VLOOKUP( ) and HLOOKUP( ) require three parameters, and they have one optional parameter:

 VLOOKUP(search_for, table_range, column_number, [range_lookup]) HLOOKUP(search_for, table_range, row_number, [range_lookup])

The first parameter, search_ for , is the actual cell content that you're trying to find. The second parameter, table_range , is the rectangular grid of cells that contains all the data you're searching through. The column_number and row_number parameters are known as offsets . Once the VLOOKUP( ) or HLOOKUP( ) function finds the requested data, the offset tells Excel how many cells to move over or down in order to find related data you want to retrieve.

Imagine a spreadsheet filled with customer records, each listed in its own row. You may initially use VLOOKUP( ) to find a customer's last name , but what you're really interested in is how much money she owes you. To find that crucial piece of info , you'd use the offset argument to retrieve the value from a cell that's a few columns away from the last name column.

Finally, the range_lookup parameter is a Boolean (true or false) value. If you specify TRUE, or leave out this parameter, then Excel finds approximate matches. If you specify FALSE, Excel gives you either an exact match or, if it finds no match, the error value #N/A. (You can also substitute 1 for TRUE or 0 for FALSE, if you find that's easier.)

12.1.1. VLOOKUP( ): Vertical Lookups

The simplest way to understand how lookup functions work is to study one of these functions in action. Looking at VLOOKUP( ) is a good place to start. Figure 12-1 shows a worksheet that uses VLOOKUP( ) to let spreadsheet viewers learn more about the products contained in a long list of exotic grocery store items. The idea here is to offer a kind of interactive information dashboard on top of the spreadsheet so that anyone can enter a product's ID number and then view specific information about the product (such as the value of the inventory in stock).

True, you could simply construct your worksheet so this information appears alongside the product data that's already listed. But when you have multiple scenarios that you want to let people play around with, your spreadsheet can get more crowded than a cross-town bus at 5:30 PM. The functions in this chapter offer a better way to offer customizable views for large amounts of information.

Here's a blow-by-blow breakdown of what's happening in the Figure 12-1 spreadsheet. The key cells are B2 and cells C4 through C11. B2 is where whoever's reading the spreadsheet types in the product ID. C4 through C8 all contain different versions of the LOOKUP( ) function, which retrieve Product Name, Unit Price, the number of units In Stock, the number of units On Order, and the Reorder Level, respectively. The actual calculations, which are pretty straightforward formulas, take place in cells C9, C10, and C11.

Figure 12-1. VLOOKUP( ) in action: The person using this spreadsheet simply types in the appropriate product number in cell B2, and then the lookup formulas in cells C4 through C8 automatically retrieve different kinds of price and inventory data (which is listed starting in row 15). The cells C9 through C11 don't need any lookup functionsthey simply use the values from the lookup cells to perform additional calculations.

Consider how Excel retrieves the product name, shown in cell C4. The formula is:

 =VLOOKUP(B2, A15:F81, 2, FALSE)

Here's what you're looking at:

The first parameter, B2, is the product ID number (as typed in by the person reading the spreadsheet). This is the value VLOOKUP( ) is seeking.
The second parameter is the most important: It gives the cell range where Excel should search. This example has 66 product rows (from row 15 to 81) and six columns of data (from column A to F). The first column in this cell range must be the column that contains the data you're trying to locate (in this case, the product ID). The other columns contain the remaining information Excel is going to retrieve once it finds a match.
The third parameter, 2, indicates the position of the data you want to retrieve. Excel numbers each column in the range that you supply from left to right, starting at 1. Thus, 2 represents the Product Name column.
Finally, FALSE indicates that you're insisting on an exact match to ensure you have the correct product.

Now, if someone typed the product ID 21 into cell B2, for instance, then each of the VLOOKUP( ) functions contained in cells C4 through C8 finds the requested row and then gives you the data they're designed to retrieve (Product, Price, In Stock, and On Order, respectively).

Tip: If you supply a range lookup value of TRUE, or if you omit this parameter altogether, Excel accepts a partial match when it performs the search. That is, it attempts to find an ID value that matches exactly, but if it can't find a match, it uses the closest ID value that's less than the lookup value B2. As a result, it's always a good idea to explicitly turn off range matching if you're trying to look up specific items.

Once you've retrieved the information you need, it's fairly easy to use it in other ordinary calculations. For example, the formula in cell C9 (Figure 12-1) calculates the total value of all the products on order by multiplying the retrieved unit price with the retrieved number of units on order:

 =C6*C7

Note: Perhaps the most convenient feature of a worksheet that uses lookup functions is that the worksheet has to store only one copy of the data. So, in the previous example, if you modify the product list, the information you get from any linked lookup formulas updates automatically.

POWER USERS' CLINIC
Advanced Text Lookup

The product lookup example shown in Figure 12-1 searches by product ID, which is a numeric value. You can also use lookup formulas to search for text . You could modify the product lookup example to conduct searches using a product's name. The price lookup formula would then look like this:

 =VLOOKUP(B2, B15:F81, 2, FALSE)

This version of the price lookup formula has two changes (compared to the one in Section 12.1.1). First, the range of searched data starts at B15 instead of A15. That's because the column that Excel searches must always be the leftmost column. The second change is the offset. Because the first column is B, the VLOOKUP( ) function numbers that column as 1, and the adjacent Price column as 2 (instead of 3). Consequently, you need to modify your offset accordingly .

Text lookups are useful, but you may find them a bit cumbersome. As a rule of thumb, when you perform a text search, you should always specify FALSE for the range lookup parameter to prevent some quirky behavior that occurs with unsorted lists. In this example, the person using the spreadsheet would need to type in the entire product name exactly as it appears in the spreadsheet, in order for the VLOOKUP( ) function to match it successfully.

You have one workaround for this limitation: the ever-popular asterisk (*) wildcard. If you add this to the end of the search value, then the VLOOKUP( ) function finds any cell that starts with this text. If you type in Queso* , then the VLOOKUP( ) function matches the Queso Cabrales product. Actually, you can go one step further, and change your lookup formulas so they always accept partial matches. The trick is to add the asterisk onto the end of the cell value in your formula, as shown here:

 =VLOOKUP(B2&"*", B15:F81, 2, FALSE)

This formula uses the ampersand (&)a special operator for joining text. For more information about manipulating text, see Chapter 11.

12.1.2. HLOOKUP( ): Horizontal Lookups

Now that you've mastered vertical lookups, it's a snap to perform horizontal lookups. The key distinction is that HLOOKUP( ) searches across the first row in the cell range you supply. You use the offset to retrieve data from other rows in the matching column.

Figure 12-2 shows an example of HLOOKUP( ) used with a grid of product information:

 =HLOOKUP(C11,A1:E8,2)*C12

Figure 12-2. Horizontal lookups are just as straightforward as vertical lookups; the direction is the only thing that changes. Here, Excel uses the function HLOOKUP to scan the top row of product names .

This formula tells Excel to search the range of cells from A1:E8. Because this is a horizontal lookup, Excel starts out by inspecting the first row (cells A1 to E1), where it attempts to find a cell that matches the content in C11 (Queso Cabrales) . When Excel finds a match, it uses the offset of 2 to step down to the second row and retrieve the cell value with the product price (21) . Finally, the formula multiples the retrieved price with the number of units (100) , which have been entered in cell C12.