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
Search JabSto ::




Custom Search