Microsoft Office Tutorials and References
In Depth Information
INDEX (Reference Form)
A
Range
B
Row
C
Column
Figure 9.5
The INDEX function in
the form of an array
creates database
function flexibility by
allowing you to specify a
range of columns and
rows and looking up an
intersecting point.
INDEX (Reference Form)
Based on the intersection of a particular row and column, INDEX (Reference Form) returns
the reference of the cell.
=INDEX(reference,row_num,column_num,area_num)
The INDEX function in the form of a reference is one of the more useful lookup functions in
that it can be used with other functions or form controls, as a cell reference, or with text
reference. To illustrate, Figure 9.6 shows three examples using the index function on a list.
The first example: =INDEX(D7:D17,6) results in 6, because it is the sixth row down on the
single-column range.
The second example is used with the MATCH function to match text from another column
shown as: =INDEX(E6:F17,MATCH(E21,E6:F17,),MATCH(“PRODUCT”,E6:F6,)) .
The result is the product name, where E6 through F17 specifies the range of the ISBN
number and the product names, the first nested match function specifies the ISBN input
number cell reference. The MATCH range includes the same range as the index, and the
second nested MATCH function calls out the column title heading in quotes and specifies
the column heading range. Notice the last example, it takes into account a form control.
The form control cell reference link is in cell D27, and indexes down to the fifth record and
returns the index range of the fifth record in the range from G7:G17. When formatting the
form control, use the F7:F17 product range. To learn more about form controls and functions
see Chapter 2, “Managing Your Business with Functions.”
Search JabSto ::




Custom Search