Category list

Finding Data with INDEX

Both the INDEX function and the OFFSET function return data from a subset of an array. They differ in several ways, including what it is you want to accomplish:

It's typical to use OFFSET when you can specify several rows and columns that you want to get at. For example, you might use this
```
=OFFSET(BaseCell, 0, 0, 2, 2)
```
to get the cells in the first two rows and the first two columns of a range of cells .
It's typical to use INDEX when you can specify a particular row or column and you want the value where they intersect. For example, this
```
=INDEX(D4:E6, 3, 2)
```
returns the contents of cell E6; that is, the third row of the second column of the range D4:E6. See Figure 2.4.

Figure 2.4. Using INDEX in this way is similar to using a formula with constants, such as =6 + 2--its usefulness is limited.

That last example, finding the value in the third row and second column of a range of cells, is almost trivial. How many times do you think you'll need to find that your company sold 1882 laptops in 2004? The INDEX function begins to get really useful when you combine it with other functions, which tell INDEX where to look. One such is MATCH.

Finding Data with MATCH

OFFSET and INDEX both return values from a range--more generally , from an array--given information about where to look. In contrast, MATCH tells you where in a range to look, given information about values.

NOTE

What's the difference between an array and a range? An array refers to any arrangement of one or more rows crossed by one or more columns . It needn't exist on a worksheet. It could be and often is a memory array used by code written in VBA (or C or FORTRAN or Java); your VBA code can use the worksheet functions described here on memory arrays. A range is an array that's located on a worksheet. You can, and often do, use VBA to move data back and forth between worksheet ranges and memory arrays.

For example, this formula

=MATCH(20,$B:$B,0)

says to find the value 20 in the range $B$2:$B$6 and return 20's position in that range. The third argument, 0, means that you want an exact match (see Figure 2.5).

Figure 2.5. If you ask MATCH for an exact match, and it can't find one, it returns the error value #N/A.

graphics/02fig05.gif

As Figure 2.5 shows, you can use MATCH to find an exact match or an approximate match. In the figure, cells E2 and E4 use MATCH to find an exact match. Cell E6 uses 1 as its third argument, to find an approximate match. Notice that

In cell E2 and E4, MATCH's third argument is 0 (zero). This requests an exact match.
In cell E2, MATCH successfully finds an exact match; 20 is in the second position in the range, so MATCH returns 2 (to indicate the second position).
In cell E4, MATCH cannot find an exact match; 25 is not found in any position in the range. MATCH therefore returns the error value #N/A.
In cell E6, MATCH's third argument is a 1. This value does two things: It asks for an approximate match, and it promises that the range's values are sorted in ascending order.

The phrase approximate match is only approximately accurate. When you supply a 1 as MATCH's third argument, you tell MATCH to find the largest value that is less than or equal to the value you're looking for.

Suppose that you're looking for the value 5 in the array { 2, 4, 6, 8} . Supplying a 1 as MATCH's third argument causes MATCH to return 2. The value 4 is the array's largest value that is equal to or less than 5, and 4 is in the second position.

It's important to remember that the sort order of the array can make a difference to an approximate match. Again, suppose that you're looking for 5, but that the array is { 2, 4, 6, 8, 5} . MATCH still returns 2 (the position that 4 occupies), even though the value 5 itself is in the array. By giving 1 as the third argument, you've promised that the array is in ascending order, so when it finds the 6, MATCH assumes that it doesn't need to look any further. If, as promised , the array is in ascending order, MATCH won't find a value that is less than or equal to 5 by looking past 6. You have to keep your promises.

Flylib.com

Category list

Similar pages

Finding Data with INDEX