Section 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 columns to the left of the lookup column, and you can't use HLOOKUP( ) to retrieve rows above the lookup row.

• You must choose between horizontal and vertical lookups. There's no way to perform a lookup that finds cells based on a lookup column and a lookup row.

In this section, you'll learn how to skirt these restrictions with the help of other functions.

Tip: There are certain lookup rules you simply can't get around. For example, the lookup functions aren't much use if there are 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, you should probably opt for Excel's list feature (Section 13.1), which provides filtering capabilities.

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

• 1 . 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 numbers or text values that are ordered in ascending order. For example, if you use the formula =MATCH(100, {12,23,48,101,321}, 1) , the result is 3 because the third value in the range ( 48 ) is the largest number less than 100.

• -1 . 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 =MATCH("b", {"doughnut","cracker"," banana ","apple"}, -1) , the result is 3 because the third value in the range (banana) is the smallest text value that falls after b in dictionary order.

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, 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 cell in this range). If it finds no match, MATCH( ) returns #N/A.

11.2.2. INDEX( ): Retrieving the Value from a Cell

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 next section shows you how useful that feature can be. 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]

For example, consider the following formula:

=INDEX(A2:A10, 3)

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.

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, then Excel designates E10 as index number 1, E11 as index number 2, and so on. The actual row number in the worksheet is unimportant.

11.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( ).

For example, consider the worksheet in Figure 11-3. Here, Excel performs the lookup using column B (the product name ), and the formula retrieves information from column A (the product ID). This is 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) Figure 11-3. This worksheet is designed to let you search for a product by name, and then retrieve its corresponding ID information. Because this information is to the left of the lookup column, VLOOKUP( ) won't help. The solution is to combine MATCH( ) and INDEX( ), so that MATCH( ) gives you the offset row number from the Product Name column. INDEX( ) then uses that value in the ID column. Excel displays the final resultthe product IDin cell C4, which contains the formula that's doing all the work. In essence, this formula breaks the problem down into two questions. The first question is "How far down is this product name?" The second question is "What product ID is just as far down?" MATCH( ) answers the first question, while INDEX( ) answers the second.

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.

11.2.4. Performing a Double Lookup

Another shortcoming with the VLOOKUP( ) and HLOOKUP( ) functions is that you can't use them simultaneously . For example, these functions won't help if you want to write a formula that finds a cell at the intersection of a specific column and row heading. You can solve this problem with the INDEX( ) function, which is much more flexible than either the VLOOKUP( ) or the HLOOKUP( ) function.

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. Figure 11-4. The formula in cell c11 performs a double lookup by using MATCH( ) twice to get the offsets, and INDEX( ) once to retrieve the value. This works because the INDEX( ) function accepts both a row and a column offset.

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 on Section 10.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 hardcoded in the formula, but you can just as easily retrieve this information from another cell.

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

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

2. Launch the Lookup Wizard by selecting Tools Lookup.

The Lookup Wizard dialog box opens (Figure 11-5, top).

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

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

5. 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. Figure 11-5. You can get help writing lookup formulas with Excel's Lookup Wizard. It walks you through a series of questions, during which you select the range you're searching and the values you're looking for. One nice feature is the ability to insert the lookup arguments into separate cells. This way, readers can search your spreadsheet for different data just by editing these cells.

6. Finally, choose where you want to put the formula (by clicking on the worksheet), and click Finish.

If you opted to insert a formula plus the lookup parameters, you'll go through two additional steps to fill the cells with the lookup criteria.

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.

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

11.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. 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 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. That range is then passed to the SUM( ) function. The end result is equivalent to this formula:

=SUM(D2:D8)

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.

11.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. These are described in Table 11-1.

Table 11-1. Miscellaneous Reference and Lookup Functions

Function

Syntax

Description

AREAS( )

AREAS(reference)

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 enclosed within double parentheses. For example, =AREAS(A1) returns a value of 1; =AREAS((A1, B3:D5, E8)) returns a value of 3.

COLUMN( )

COLUMN(reference)

Returns the column number for a reference. For example, =COLUMN(B3) returns the number 2, which represents column B.

COLUMNS( )

COLUMNS(range)

Counts the number of columns in a range.

ROW( )

ROW(reference)

Returns the row number for a reference. For example, =ROW(B3) returns the number 3.

ROWS( )

ROWS(range)

Counts the number of rows in a range.

CHOOSE( )

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 first position in the list of values that follows .

LOOKUP( )

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 reduces the usefulness of this function.

11.2.8. INDIRECT( ) and ADDRESS( ): Working with Cell References Stored as Text

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:

=A1+A2

if you were to use INDIRECT( ) to refer to cell A1, you'd write the formula this way:

=INDIRECT("A1")+A2

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 hardcoding your cell references (as in =A1+A2 ), you might 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 11-6 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. Figure 11-6. This worksheet shows the quirky INDIRECT( ) function at work. Here, a dynamic SUM( ) formula is used to add a range of cells. The SUM( ) formula uses the INDIRECT( ) function to read the cell references stored in cells D1 and D2. It then uses these to create the range of cells that are added together.

Here's an example of this technique:

=INDIRECT(A1)+INDIRECT(A2)

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:

=INDIRECT("B2")+INDIRECT("D8")

which is the same as:

=B2+D8

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 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. For example, 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. Figure 11-7. This worksheet shows three attempts at using INDIRECT( ). In C7 (whose formula is written out in B7), the cell reference (C2) is a hardcoded piece of text. The formula displays the value contained in cell C2. In C8, there's an extra layer of redirection. The formula grabs the content in C1, which is the piece of text "C2", and supplies that to the INDIRECT( ) function. The INDIRECT( ) function then displays the content from cell C2, as in the previous example. The final =INDIRECT("C2") formula grabs the content from C2, which isn't a cell reference (it's the number 23). As a result, the INDIRECT( ) function accomplishes nothing.

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

11.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 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. Figure 11-8. In this worksheet, the TRANSPOSE( ) function has inverted most of the data in a table, turning columns into rows and rows into columns.

The best way to get started with the TRANSPOSE( ) function is to try a simple example. Just follow these steps:

1. Find a table of cells you want to transpose. Make a note of its size in rows and columns.

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.

2. Move to the area where you want to insert the transposed cells, and select the appropriately sized area.

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

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

4. Commit the function by pressing Ctrl+Shift+Enter.

This step inserts the formula into all the selected cells as an array formula.  Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon