Excel INDEX Function - Array Form

By Ted French, About.com Guide

Excel INDEX Function - Array Form

There are two forms of the INDEX function: the Array Form and the Reference Form.

The Array form of the function returns the value from an array located at the intersection point of a specific row and column of data.

The INDEX Function Syntax and Arguments

The syntax for the INDEX function is:

= INDEX (Array, Row_num, Column_num)

Array - The selected range of cells that is searched by the function.

Row_num (optional) - The row number in the array from which to return a value. If this argument is omitted, Column_num is required.

Column_num (optional) - The column number in the array from which to return a value. If this argument is omitted, Row_num is required.

Example Using Excel's INDEX Function to Find Data

As can be seen in the image above, this example will use the Array Form of the INDEX function to return the term Widgets from the inventory list.

Entering the Tutorial Data

1. Enter the following data into cells D1 to F5.

Cell    Data
 D1  -  Parts Inventory
 D2  -  Item

 E2  -  List
 E3  -  Bolts
 E4  -  Washers
 E5  -  Screws

 F3  -  Cogs
 F4  -  Bearings
 F5  -  Widgets 

Entering the Function's Arguments

2. Click on cell D3 in the worksheet - this is where the results of the function will be displayed


3. Click on the Formulas tab of the ribbon menu


4. Choose Lookup and Reference from the ribbon to open the function drop down list


5. Click on INDEX in the list to bring up the function's dialog box


6. In the dialog box, click on the Array line


7. Highlight cells E3 to F5 in the worksheet to enter the range into the dialog box


8. Click on the Row_num line in the dialog box


9. Enter the number " 3 " (no quotes) on this line since we are looking for data in the third row of the array


10. Click on the Column_num line in the dialog box


11. Enter the number " 2 " (no quotes) on this line since we are looking for data in the second column of the array


12. Click OK to complete the function and close the dialog box


13. The word Widgets appears in cell D3 since it is the term in the cell intersecting the third row and second column of the parts inventory


14. When you click on cell D3 the complete function =INDEX (E3:F5, 3, 2) appears in the formula bar above the worksheet