12.2. Advanced Lookups
VLOOKUP( ) and HLOOKUP( ) work well for linking together tables in a worksheet. They also impose a few restrictions, however, including:
In this section, you'll learn how to skirt these restrictions with the help of other functions.
Tip: You simply can't get around certain lookup rules. The lookup functions aren't much use if you have potentially multiple matches. That means you can't use a lookup function to retrieve your top-10 selling products, for example. If you want to use this sort of logic, then you should probably opt for Excel's list feature (Chapter 14), which provides filtering capabilities.
12.2.1. MATCH( ): Finding the Position of Items in a Range
The MATCH( ) function lets you find the position of an item in a range. On its own, MATCH( ) doesn't accomplish a whole lot, but when used in conjunction with some of the functions described later in this section, you'll find it's really handy. Here are some MATCH( ) fundamentals.
To use Match( ), you simply specify the search value (either a number or text) and the range you want to search:
MATCH(search_for, range, [match_type])
The range you use must be one-dimensional. That means you can search through the column of cells A1:A10 or the row of cells A1:E1, but you can't search the grid of cells A1:E10.
The match_type argument's optional, but highly recommended. It can take one of these three values:
Note: You'll almost always want to use a match_type of 0. However, the MATCH( ) function uses a match_type of 1 if you omit the match_type parameter. Consider yourself warned .
If MATCH( ) finds the value you're searching for, then it gives you a number indicating its position. If you're searching the range of cells A1:A10, and the search item's in cell A3, then the MATCH( ) function returns 3 (because it's the third cell in this range). If it finds no match, MATCH( ) returns #N/A.
12.2.2. INDEX( ): Retrieving the Value from a Cell
You may have already noticed that the MATCH( ) function really does half the work of the VLOOKUP( ) and HLOOKUP( ) functions. It can find the position of the search item, but it gives you the position as an index number, instead of the actual cell content. (Index 1 is the first item in a range, 2 is the second, and so on.) The MATCH( ) function becomes useful only when you combine it with another function, like INDEX( ).
INDEX( ) is powerful not only because it can retrieve the actual cell content but also because it lets you move to any row or column you want. The next section shows you how useful that feature is. But first, here's a quick look at how INDEX( ) works.
INDEX( ) gives you a value from a range of cells, using the index number you specify:
INDEX(range, row_number, [column_number]
Consider the following formula:
This formula returns the content of the third cell in the range: A4. If you supply a two-dimensional range, you need to give a row and column index. Here's an example:
=INDEX(A2:B10, 3, 2)
This formula gives you the cell in the third row and the second column, which is B4.
Note: Remember that the index numbers used by MATCH( ) and INDEX( ) are offsets, not actual cell references. In other words, if you specify a range that starts on cell E10, Excel designates E10 as index number 1, E11 as index number 2, and so on. The actual row number in the worksheet doesn't matter.
12.2.3. Performing a "Left Lookup"
On their own, MATCH( ) and INDEX( ) are just curiosities. But when you combine them, you have the ability to overcome many of the limitations inherent in VLOOKUP( ) and HLOOKUP( ).
Consider the worksheet in Figure 12-3. Here, Excel performs the lookup using column B (the product name ), and the formula retrieves information from column A (the product ID). This example's a left lookup , something that isn't possible using the VLOOKUP( ) function.
To solve this problem, you first use MATCH( ) to find the position of the product name you're looking for:
=MATCH(B2, B8:B74, 0)
This formula gives you a value of 8 because the searched for value (Tofu) is located in the eighth row of the cell range you're searching. Next, you can use the value from the MATCH( ) function to retrieve the cell content by employing the INDEX( ) function. The trick is that the INDEX( ) function uses a range that covers all the rows and columns in the table of data:
=INDEX(A8:F74, match_result, 1)
Here's how you'd combine these functions into a single formula:
=INDEX(A8:F74, MATCH(B2,B8:B74,0), 1)
Tip: Remember, the INDEX( ) function lets you perform a lookup in a column or a row.
12.2.4. Performing a Double Lookup
Another shortcoming with the VLOOKUP( ) and HLOOKUP( ) functions is that you can't use them simultaneously . These functions don't help if you want to write a formula that finds a cell at the intersection of a specific column and row heading. Imagine you want to write a formula to find the number of sales recorded in January 2004 at the London location, in the spreadsheet shown in Figure 12-4. You can solve this problem with the INDEX( ) function, which is much more flexible than either the VLOOKUP( ) or the HLOOKUP( ) function.
Note: With just a tad more work, you can write a formula that lets readers of your spreadsheet indicate which city and which month they're interested in seeing sales figures for. Those steps are covered in the next section.
Because you need to look up the rows and columns, you'll need to use the MATCH( ) function twice.
First, you'd use the MATCH( ) function to find the row that has the January sales figures:
=MATCH(DATE(2004,1,1), A2:A8, 0)
Note: Remember that the DATE( ) function, as explained in Section 11.4.2, creates a date without your needing to know its underlying serial number.
Next, you'd use MATCH( ) to find the column with the sales figures for the London office:
=MATCH("London", B1:D1, 0)
With these two pieces of information, you can build an INDEX( ) function that searches the whole range for the appropriate sales figure:
=INDEX(B2:D8, MATCH(DATE(2004,1,1),A2:A8,0), MATCH("London",B1:D1,0))
In this example, the search values (the date and the store location) are hard-coded in the formula, but you can just as easily retrieve this information from another cell.
12.2.5. The Lookup Wizard
If you're becoming a little frazzled from considering all the different types of lookup functions, you may be glad to learn that Excel includes a tool that builds lookup functions for you automatically . It's called the Lookup wizard.
Here's how you'd use the Lookup wizard to build the sales lookup formula used in the previous example.
Here's what the completed formula could look like, if you chose to let the Lookup wizard also use lookup parameters:
=INDEX($A:$D, MATCH(F5,$A:$A,), MATCH(F4,$A:$D,))
As you can see, this formula uses the MATCH( ) and INDEX( ) function, just as in the previous section. However, it doesn't supply the recommended match_type parameter. It's a good idea to edit the formula by setting the match_type to 0, in order to ensure that any data your readers type into the spreadsheet matches your lookup parameters exactly:
=INDEX($A:$D, MATCH(F5,$A:$A,0), MATCH(F4,$A:$D,0))
Excel's version of the formula also uses absolute references (Section 8.3.1), which ensures that the cell reference doesn't change if you copy the formula to another area on the worksheet.
Note: The Lookup wizard works wonders if your data is set out in a typical table with column and row headings, but it's less flexible when your data's organized in an unconventional way, or if you need to perform a lookup in two different areas on the worksheet. In the latter cases, the INDEX( ) and MATCH( ) functions are still up to the task, although you'll need to exert a little more brainpower creating a formula that uses them.
12.2.6. OFFSET( ): Moving Cell References to a New Location
Not surprisingly, Excel gives you another way to solve lookup problems. The OFFSET( ) function lets you take a cell reference and move it to a new location. If you take the cell reference A1, and use OFFSET( ) to move it down two rows, then Excel would refer to it as cell A3.
For the most part, everything you can accomplish with the OFFSET( ) function you can also achieve by combining INDEX( ) and MATCH( ). It's really a matter of preference. Some Excel gurus prefer to use INDEX( ) and MATCH( ), while others find that OFFSET( ) makes for clearer formulas. Once you've finished this section, you'll be able to make your own choice.
The OFFSET( ) function requires three arguments: the cell reference you want to move, the number of rows you want to move, and the number of columns you want to move:
OFFSET(reference, rows_to_offset, cols_to_offset, [height], [width])
The following formula moves the reference A1 to A3, and then returns whatever content's in that cell:
=OFFSET(A1, 2, 0)
To move a row upwards or to a column that's to the left, specify a negative number for the rows_to_offset or cols_to_offset parameter, respectively. You can also use the height and width parameters to expand the cells your reference covers, converting it from a single-cell reference into a full-fledged range. Here's one example:
=SUM(OFFSET(D2, 0, 0, 7, 1))
This formula uses OFFSET( ) to expand a reference (D2) to a range containing seven cells (D2:D8). The original cell serves as the "anchor point" that becomes a corner in the range. Then Excel passes on that range to the SUM( ) function. The end result is equivalent to this formula:
Now that you understand OFFSET( ), you can use it in place of the INDEX( ) function in most situations. You just need to start at the top-left corner of your table, and use the OFFSET( ) function to get to the cell you want.
Consider the formula in Figure 12-3, which performs a left lookup on the product list:
=INDEX(A8:F74, MATCH(B2,B8:B74,0), 1)
You can easily rewrite this using the OFFSET( ) function, as shown here. Cell A8, at the top-left corner of the table, is the starting point:
=OFFSET(A8, MATCH(B2,B8:B74,0)-1, 0)
Like those in the INDEX( ) section (Section 12.2.3), this formula still lets you search through the entire table of data to find the Product Name (using the Product ID value you typed in). In fact, there's really no meaningful difference between the two functions. In most cases, you can use OFFSET( ) or INDEX( ) interchangeably. One exception is that only the OFFSET( ) function has the ability to convert a single-cell reference into a range.
12.2.7. Other Reference and Lookup Functions
MATCH(), INDEX( ), and OFFSET( ) are definitely the three most useful reference and lookup functions. However, Excel also provides a few more similar functions that occasionally come in handy. Table 12-1 gives you a quick tour.
Table 12-1. Miscellaneous Reference and Lookup Functions
12.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored As Text
INDIRECT( ) and ADDRESS( ) are two of Excel's strangest reference functions. They let you work with cell references stored as text.
The INDIRECT( ) function retrieves the content from any cell you specify. The twist is that you specify the cell using literal text (a string) to describe the location of the cell. So, where normally you would add two cells this way:
if you were to use INDIRECT( ) to refer to cell A1, you'd write the formula this way:
Note the quotation marks around the cell name; they indicate that A1 is just a piece of text, not a cell reference. The INDIRECT( ) function examines this text and gives you the corresponding cell reference.
The obvious question is why would anyone bother to specify cell references as ordinary text? The most common reason is because you want to create a formula that's extremely flexible. So, for example, instead of hard-coding your cell references (as in =A1+A2), you may want your formula to read another cell to find out which cell reference it should use. The end result is that the person reading the spreadsheet can change a cell reference used in a calculation, without needing to edit a complex formula by hand. Figure 12-8 shows a worksheet that demonstrates this trick. It lets the person using the spreadsheet choose the numbers to add, without forcing him to write a SUM( ) function by hand.
Here's an example of this technique:
This formula does not add the content of cells A1 and A2. Instead, it looks in the cells A1 and A2 to determine which two cells it should add together. If the cell A1 contains the text "B2" and the cell A2 contains the text "D8", the formula becomes the following:
which is the same as:
This formula assumes that there are two cell references, entered as text, in cells A1 and A2. If there's something else in these cells, the INDIRECT( ) function doesn't work. If cell A1 contains a number, for instance, the formula INDIRECT(A1) returns the error code #REF! (which is Excel shorthand for, "I expected a cell reference, but all you gave me was a lousy number"). Figure 12-9 shows what you canand can'tdo with the INDIRECT( ) function.
The ADDRESS( ) function performs a similarly strange operation, but in reverse. You supply ADDRESS( ) with row and column index numbers, and Excel gives you a piece of text that contains the cell reference. The following formula returns $B$1:
Remember, all the ADDRESS( ) function does is return a cell reference as a string. This piece of text isn't too useful; all you can really do with it is display it in a cellor, if you're really warped , pass it to the INDIRECT( ) function to convert it back to a real cell reference.
Consider the example shown in Figure 12-4, which uses a lookup function to find the sales in a particular city on a particular month. The formula looked like this:
=INDEX(B2:D8, MATCH(DATE(2004,1,1),A2:A8,0), MATCH("London",B1:D1,0))
Instead of displaying the actual sales figures, you could use the ADDRESS( ) function to display the cell reference for the spreadsheet reader to see, like so:
This formula displays the text $C$4. If you want to get craftier, then you could add some additional text by modifying the formula like this:
="The number you are looking for is in cell " & ADDRESS(MATCH(DATE(2004,1,1),A2:A8,0), MATCH("London",B1:D1,0))
This formula displays the text The number you are looking for is in cell $C$4 . Clearly, you won't use a formula like this often, but it does raise some interesting possibilities.
If you don't want the ADDRESS( ) function to return a string with an absolute reference (Section 8.3.1), then you can supply a third parameter, called abs_number . It takes one of the following values:
12.2.9. TRANSPOSE( ): Changing Rows into Columns and Vice Versa
TRANSPOSE( ) is an interesting function that you can use to change the structure of a table of data. Using TRANSPOSE( ), you can invert the data so that all the rows become columns, and the columns become rows (Figure 12-10). In that respect, it works the same as the Transpose option in the Paste Special dialog box, as described in Figure 3-8 in Section 3.2.4. The Paste Special approach, however, creates a distinct copy of the data. The TRANSPOSE( ) function, on the other hand, creates a linked table that's bound to the original data, which means that if you change the original table, the transposed table also changes. The TRANSPOSE( ) function is, therefore, ideal for showing more than one representation of the same data.
The best way to get started with the TRANSPOSE( ) function is to try a simple example. Just follow these steps: