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