| 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$2:$B$6,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. 
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. |
