Find Data in a List With the INDEX Function

Use this formula for arrays to find data

Click index in the formula bar.

Kaitlyn Baker / Unsplash

 

In Excel, the INDEX function is used to find and return a specific value or find the cell reference to the location of that value in a worksheet.

Instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.

Two forms of the INDEX function are available in Excel:

  • INDEX array form 
  • INDEX reference form

The main difference between the two forms of the function is:

  • The array form returns the data value located at the intersection point of a particular row and column of data.
  • The reference form returns the cell reference of the intersection point of a particular row and column.

Excel INDEX Array Form

An array is a group of adjacent cells in a worksheet. In the image above, the array is the block of cells from A2 to C4.

In this example, the array form of the INDEX function located in cell C2 returns the data value — Gizmo — found at the intersection point of row 3 and column 2.

The INDEX Array Form Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the INDEX function is:

= INDEX (Array, Row_num, Column_num)

  • Array: The cell references for the range of cells to be searched by the function for the desired information
  • 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.

For both the Row_num and Column_num arguments, either the actual row and column numbers or the cell references to the location of this information in the worksheet can be entered.

INDEX Array Form Example

Options for entering the function and its arguments include:

  • Typing the complete function: =INDEX(A2:C4,B6,B7) into cell B8, or
  • Selecting the function and its arguments using the INDEX function dialog box

Although it is possible to type the complete function in manually, many people find it easier to use the dialog box to enter a function's arguments.

The steps below use the dialog box to enter the function's arguments.

Opening the Dialog Box

Since there are two forms of the function — each with its own set of arguments —each form requires a separate dialog box.

As a result, there is an extra step in opening the INDEX function dialog box not present with most other Excel functions. This step involves picking either the Array form or Reference form set of arguments.

Below are the steps used to enter the INDEX function and arguments into cell B8 using the function's dialog box.

  1. Enter the data as shown into a new worksheet in Excel.

    Screenshot of data in cells A1:C4
  2. Select cell B8 in the worksheet. This is where the function will be located.

    Screenshot of cell B8 selected
  3. Choose the Formulas tab of the ribbon.

    Screenshot of Formulas tab in Excel
  4. Select Lookup and Reference to open the drop-down list.

    Screenshot of Lookup & Reference drop-down list
  5. Choose INDEX to bring up the Select Arguments dialog box where you choose between the Array and Reference forms of the function.

    Screenshot of Select Arguments dialog box
  6. Select the array, row_num, column_num option and choose OK to open the INDEX function - Array form dialog box.

Entering the Function's Arguments

  1. In the Formula Builder dialog box, choose the Array line.

    Screenshot of Array line in the Formula Builder dialog box
  2. Highlight cells A2 to C4 in the worksheet to enter the range into the dialog box.

    Screenshot of A2:C4 selected
  3. Select the Row_num line in the dialog box.

    Screenshot of Row_num field
  4. Enter 3 into the dialog box.

    Screenshot of 3 in Row_num line
  5. Select the Column_num line in the dialog box.

  6. Choose cell B7 to enter that cell reference into the dialog box.

    Screenshot of B7 in Column_num line
  7. Select Done to complete the function and close the dialog box.

The word Gizmo appears in cell B8 because it is the term in the cell intersecting the third row and second column of the parts inventory.

Screenshot of Gizmo in B8

When you select cell B8, the complete function =INDEX(A2:C4,B6,B7) appears in the formula bar above the worksheet.

Index Function Error Values

Common error values associated with the INDEX array form function are:

  • #VALUE! occurs if either the Row_num or Column_num arguments are not numbers.
  • #REF! occurs if either the Row_num argument is greater than the number of rows in the selected range or the Col_num argument is greater than the number of columns in the selected range.

Excel INDEX Reference Form

The reference form of the function returns the data value of the cell located at the intersection point of a specific row and column of data.

The reference array can be consist of multiple nonadjacent ranges as shown in the image above.

The INDEX Reference Form Syntax and Arguments

The syntax and arguments for the INDEX function Reference form are:

= INDEX (Reference, Row_num, Column_num, Area_num)

  • Reference (required) is the cell references for the range of cells to be searched by the function for the desired information. If multiple, nonadjacent ranges are entered for this argument, the ranges must be surrounded by a separate set of round brackets as shown in the INDEX formula: =INDEX((A1:A5,C1:E1,C4:D5),B7,B8) taken from the image above.
  • Row_num is the row number in the array from which to return a value. It is optional for single row Reference ranges but required for single column and multiple row Reference ranges.
  • Column_num is the column number in the array from which to return a value. It is optional for single column Reference ranges but required for single row and multiple column Reference ranges.

For both the Row_num and Column_num arguments, either the actual row and column numbers or the cell references to the location of this information in the worksheet can be entered.

  • Area_num (optional). When the Reference argument contains multiple nonadjacent ranges, this argument selects which range of cells to return data from. If omitted, the function uses the first range listed in the Reference argument. The first range entered in the Reference argument is numbered 1, the second is 2, the third is 3 and so on.

INDEX Reference Form Example

The example uses the reference form of the INDEX function to return the month July from area 2 of the range A1 to E1.

Entering the INDEX Function

Options for entering the function and its arguments include:

  • Typing the complete function: =INDEX((A1:A5,C1:E1,C4:D5),B7,B8) into cell B10, or
  • Selecting the function and its arguments using the INDEX function dialog box

Although it is possible to type the complete function in manually, many people find it easier to use the dialog box to enter a function's arguments.

The steps below use the dialog box to enter the function's arguments.

Opening the Dialog Box

Because there are two forms of the function — each with its own set of arguments — each form requires a separate dialog box.

As a result, there is an extra step in opening the INDEX function dialog box not present with most other Excel functions.

Enter the INDEX function and arguments into cell B10 using the function's dialog box.

  1. Enter the data as shown in an Excel worksheet.

    Screenshot of data for sample function
  2. Select cell B10 in the worksheet, which is where the function will be located.

  3. Choose the Formulas tab of the ribbon.

    Screenshot of Formulas tab and B10 selected
  4. Select Lookup and Reference from the ribbon to open the function drop-down list.

    Screenshot of INDEX in Lookup & Reference drop-down
  5. Choose INDEX in the list to bring up the Select Arguments dialog box where you choose between the Array and Reference forms of the function.

  6. Select the reference, row_num, column_num, area_num option.

    Screenshot of INDEX function dialog box with reference, row_num, column_num, area_num selected
  7. Choose OK to open the INDEX function - Reference form dialog box.

Entering the Function's Arguments

  1. In the dialog box, select the Reference line.

  2. Highlight cells A1:E5.

    Screenshot of A1:E5 selected for Reference line
  3. Select the Row_num line in the dialog box.

  4. Choose cell B7 to enter that cell reference into the dialog box.

    Screenshot of B7 in Row_num line
  5. Select the Column_num line in the dialog box.

  6. Choose cell B8 to enter that cell reference into the dialog box.

    Screenshot of B8 in Column_num line
  7. Select the Area_num line in the dialog box.

  8. Choose cell B9 to enter that cell reference into the dialog box.

  9. Select OK.

The month July appears in cell B10 because it is the month in the cell intersecting the first row and second column of the second area (range C1 to E1).

Index Function Error Values

Common error values associated with the INDEX function reference form are:

  • #VALUE! occurs if either the Row_num, Column_num, or Area_num arguments are not numbers.
  • #REF! occurs if the Row_num argument is greater than the number of rows in the selected range; the Col_num argument is greater than the number of columns in the selected range, or the Area_num argument is greater than the number of areas in the selected range.

Dialog Box Advantages

Advantages to using the dialog box to enter the data for the function's arguments include:

  • The dialog box takes care of the function's syntax making it easier to enter the function's arguments one at a time without having to enter the equal sign, the brackets, or the commas that act as separators between the arguments.
  • Cell references, such as B6 or B7, can be entered into the dialog box using pointing, which involves choosing selected cells with the mouse rather than typing them. Not only is pointing easier, but it also reduces errors in formulas caused by incorrect cell references.