Excel INDEX Function - Array Form
There are two forms of the INDEX function: the Array Form and the Reference Form.
The INDEX Function Syntax and Arguments
The syntax for the INDEX function is:
= INDEX (Array, Row_num, Column_num)
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
- Enter the following data into cells D1 to F5.
- Click on cell D3 in the worksheet - this is where the results of the function will be displayed
- Click on the Formulas tab of the ribbon menu
- Choose Lookup and Reference from the ribbon to open the function drop down list
- Click on INDEX in the list to bring up the function's dialog box
- In the dialog box, click on the Array line
- Highlight cells E3 to F5 in the worksheet to enter the range into the dialog box
- Click on the Row_num line in the dialog box
- Enter the number " 3 " (no quotes) on this line since we are looking for data in the third row of the array
- Click on the Column_num line in the dialog box
- Enter the number " 2 " (no quotes) on this line since we are looking for data in the second column of the array
- Click OK to complete the function and close the dialog box
- 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
- When you click on cell D3 the complete function =INDEX (E3:F5, 3, 2) appears in the formula bar above the worksheet
Cell Data D1 - Parts Inventory D2 - Item E2 - List E3 - Bolts E4 - Washers E5 - Screws F3 - Cogs F4 - Bearings F5 - Widgets