Section 11.1. The Basic Lookup | Excel 2010: The Missing Manual

11.1. The Basic Lookup

The two most popular lookup functions are VLOOKUP( ) and HLOOKUP( ). 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, row_number, [range_lookup]) HLOOKUP(search_for, table_range, column_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 row_number and column_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.

For example, imagine a spreadsheet filled with customer records, each listed in its own row. You might 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, 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.

11.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 11-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 if 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 11-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.

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

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

Figure 11-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. The cells C9 through C11 don't need any lookup functionsthey simply use the values from the lookup cells to perform additional calculations.

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 the Excel should search. In this example, there are 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, 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 will accept a partial match when it performs the search. That is, it will attempt to find an ID value that matches exactly, but if it can't find a match, it'll use 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 11-1) calculates the total value of all the products on order ("Value of Inventory") 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 there is 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 11-1 searches by product ID, which is a numeric value. You can also use lookup formulas to search for text . For example, 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 on Section 11.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 they can be 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.

There is one workaround to this limitation: the ever-popular asterisk (*) wildcard. If you add this to the end of the search value, the VLOOKUP( ) function will find any cell that starts with this text. For example, if you type in Queso* , the VLOOKUP( ) function will match 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 10.

11.1.2. HLOOKUP( ): Horizontal Lookups

Now that you've mastered vertical lookups, you can readily perform a horizontal lookup. 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 11-2 shows an example of HLOOKUP( ) used with a grid of product information:

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

Figure 11-2. Horizontal lookups are just as straight-forward as vertical lookups; the only thing that changes is the direction. Here, the function HLOOKUP( ) is used 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's 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.