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.
