Microsoft Office Tutorials and References

In Depth Information

**OFFSET**

The value used to find the specific value you want in the table.

LOOKUP_VALUE

The contiguous range of cells containing lookup values.

LOOKUP_ARRAY

Specifies whether to find an exact match (0), to find the largest

value that is less than or equal to the lookup value (1), or to find the

smallest value that is greater than or equal to the lookup value (-1).

MATCH_TYPE

Notice the example in Figure 9.11, the two formulas:

■
=MATCH(5,C7:C17,0)
where 5 is lookup value, and C7:C17 is the lookup array or range,

and the
MATCH
type is zero. The
MATCH
type is simply a mechanism Excel uses to provide

a type return in a formula. With the
MATCH
type zero, the return results in the record

position.

■
=MATCH(5,C7:C17,1)
where 5 is the lookup value, and C7:C17 is the lookup array or

range, and the
MATCH
type is 1, the return result is order of the number.

Figure 9.11

The
MATCH
function

can include the return

of a value or of text.

C

MATCH type

B

MATCH range

A

Lookup value

OFFSET

OFFSET
returns a reference to a range that is a specific number of rows and columns from a

cell or range of cells.

=OFFSET(reference,rows,columns,height,width)

The
OFFSET
function offsets a current location or position with another. For example, if you were

giving a presentation and wanted the presentation to be interactive, you could use the
OFFSET

function to return different locations for a chart reference as shown in Figure 9.12. The first

example in the figure shows the
OFFSET
function being used to look up or offset the Year 1 unit