11.2. Advanced Lookups
VLOOKUP( ) and HLOOKUP( ) work well for linking together tables in a worksheet. They also impose a few restrictions, however, including:
The lookup column must be the first column or row in the range. That means you can't use VLOOKUP( ) to retrieve
You must choose between horizontal and vertical lookups. There's no way to perform a lookup that finds
In this section, you'll learn how to skirt these restrictions with the help of other functions.
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, it can be 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 is optional, but highly recommended. It can take one of these three values:
. Gives you the position of the first item that matches exactly.
. Gives you the position of the largest value that is equal to or less than the search argument. In order for this argument to work correctly, you must perform a lookup in a range of
. Gives you the position of the smallest value that is equal to or greater than the search argument. In order for this argument to work correctly, you must perform a lookup in a range of numbers or text values that are ordered in descending order. For example, if you use the formula
If MATCH( ) finds the value you're searching for, it gives you a number indicating its position. For example, if you're searching the range of cells A1:A10, and the search item is in cell A3, the MATCH( ) function returns 3 (since it's the third
You might 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
INDEX( ) gives you a value from a range of cells, using the index number you specify:
INDEX(range, row_number, [column_number]
For example, consider the following formula:
This returns the content of the third cell in the range: A4. If you supply a two-dimensional range, you'll need to give a row and column index. Here's an example:
=INDEX(A2:B10, 3, 2)
This gives you the cell in the third row and the second column, which is B4.
On their own, MATCH( ) and INDEX( ) are just curiosities. But when you combine them, you have the ability to
For example, consider the worksheet in Figure 11-3. Here, Excel
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 (
) is located in the
=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)
Another shortcoming with the VLOOKUP( ) and HLOOKUP( ) functions is that you can't use them
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 11-4.
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)
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 hardcoded in the formula, but you can just as easily retrieve this information from another cell.
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.
First, make sure the Lookup Wizard add-in is switched on. To do so, select Tools Add-Ins, and make sure there's a checkmark next to Lookup Wizard. Click OK to continue.
You may need to insert the Office (or Excel) CD you got when you originally bought Excel in order to install the Lookup Wizard add-in. Once you switch on the Lookup Wizard, an additional menu entry named Lookup appears in the Tools menu.
Launch the Lookup Wizard by selecting Tools Lookup.
The Lookup Wizard dialog box opens (Figure 11-5, top).
Select the range of cells where you'd like to perform the lookup, and then click Next.
In the sales example, this is the range A1:D8. Make the selection by dragging across the cell range on the worksheet.
Specify both the row and column that form the intersection of the cell you're looking to identify (Figure 11-5, middle).
You can make your choices from the Wizard's two drop-down lists. Choose the column you want from the top drop-down list and choose the row from the bottom list. In the sales example, the column is London and the row is Jan-04.
Choose whether to display just the formula you've created or the formula plus the two lookup parameters (Figure 11-5, bottom). Once you've made your choice, click Next to continue.
Choosing just the formula is only moderately helpful, because the formula that the wizard creates is hard-coded with the two lookup parameters. Translation: the formula works, but it will give you only the value of the cell that lies at the intersection you've identified.
On the other hand, if you choose to display the formula plus the two lookup parameters, things get interesting. In this case, you can modify your lookup parameters at any time to identify different cells within your table. That kind of flexibility can be really helpful if you want your spreadsheet readers to specify the city and date they'd like to see.
Once again, in the sales example, the Lookup Wizard would add two extra cells (as explained in the next step)one with the text London and one with the date January 1, 2003. The lookup formula references these cells, so if you change the word London to Paris, the lookup result changes automatically.
Finally, choose where you want to put the formula (by clicking on the worksheet), and click Finish.
Here's what the completed formula might 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, ), MATCH(F4,$A:$D, ))
Excel's version of the formula also uses absolute references (Section 7.3.1), which ensures that the cell reference won't change if you copy the formula to another area on the worksheet.
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. For example, if you take the cell reference A1, and use OFFSET( ) to move it down two rows, Excel would then 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 is 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
parameter, respectively. You can also use the height and width parameters to expand the cells your reference covers, converting it from a
=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. That range is then passed 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.
For example, consider the formula in Figure 11-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. The starting point is cell A8, at the top-left corner of the table:
=OFFSET(A8, MATCH(B2,B8:B74,0)-1, 0)
Like those in the INDEX( ) section (Section 11.2.2), 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.
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. These are described in Table 11-1.
Returns the number of areas in a reference. An area is defined as a single cell or a range of contiguous cell. If you have more than one area, all the areas need to be
Returns the column number for a reference. For example, =COLUMN(B3) returns the number 2, which represents column B.
Counts the number of columns in a range.
Returns the row number for a reference. For example, =ROW(B3) returns the number 3.
Counts the number of rows in a range.
CHOOSE(index_num, value1, value2, ... )
Returns a value from a list depending on the index number. For example,
=CHOOSE(1, A1, A2, B1)
returns the content in cell A1 because that's the
position in the list of values that
LOOKUP(search_for, lookup_range, result_range)
LOOKUP( ) is a slimmed-down version of HLOOKUP( ) and VLOOKUP( ). It searches for a value in one range, and returns the value in the same index position in the second range. Unfortunately, you have no way to enforce strict matching, which
INDIRECT( ) and ADDRESS( ) are two of Excel's strangest reference functions. They allow you to 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
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 won'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 11-7 shows what you canand can'tdo with the INDIRECT( ) function.
The ADDRESS( ) function performs a similarly
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
Consider the example shown in Figure 11-4, which uses a lookup function to find the sales in a particular city on a particular month. The formula
=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, 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 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 7.3.1), you can supply a third parameter, called abs_number . It takes one of the following values:
Returns an absolute address, as in $B$1.
Fixes the row number, as in B$1.
Fixes the column, as in $B1.
Returns a relative reference, as in B1.
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 11-8). In that respect, it works the same as the Transpose option in the Paste Special dialog box, as described on Figure 3-6. 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:
Find a table of cells you want to transpose. Make a note of its
To determine the size of the transposed table, simply switch the number of rows and columns. That means a table made up of three rows and four columns will become a table made up of four rows and three columns.
Move to the area where you want to insert the transposed cells, and select the appropriately
For example, if you want to transpose a table with three rows and two columns, you'll need to select a grid of cells that's two rows high and three columns wide. You need to select all the cells before you begin because you'll be creating an array formula (Sidebar 7.6). (An array formula is required because the TRANSPOSE( ) function returns a whole table's worth of results.)
Press the equal sign (=) and begin typing the TRANSPOSE( ) formula.
The TRANPOSE( ) formula requires one argument, which is the range of cells you want to transpose. Here's an example: =TRANSPOSE(A1:E4) . Don't press Enter yet.
Commit the function by pressing Ctrl+Shift+Enter.