1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel INDEX Function - Array Form

By

Find Data in a List with the INDEX Function
Excel INDEX Function

Excel INDEX Function

© Ted French

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.
  2. 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

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

  4. Click on the Formulas tab of the ribbon menu

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

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

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

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

  9. Click on the Row_num line in the dialog box

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

  11. Click on the Column_num line in the dialog box

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

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

  14. 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

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

©2014 About.com. All rights reserved.