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.

Excel INDEX Array Form

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.

An array is a group of adjacent cells in a worksheet. In the image below, the array is the block of cells from A2 to C4. In this example, the array form of the INDEX function located in cell B8 returns the data value — Gizmo — found at the intersection point of row 3 and column 2 of the array.

Data as shown for entry in Excel

The INDEX Array Form Syntax and Arguments

The function syntax refers to the layout of the function and includes the function 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:

  • Type the complete function =INDEX(A2:C4,B6,B7) into cell B8.
  • Select the function and its arguments using the INDEX function dialog box.

Although it is possible to type the complete function manually, it's easier to use the dialog box to enter the function arguments. The steps below use the dialog box to enter the function arguments.

Open 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 to open the INDEX function dialog box that's not present with other Excel functions. This step involves picking either the Array form or the Reference form set of arguments.

To enter the INDEX function and arguments into cell B8 using the Function dialog box:

  1. Create a new Excel worksheet and enter the data as shown in the image below.

    Data as shown for entry in Excel
  2. Select cell B8 in the worksheet. This is where the function will be located.

    Cell B8 in Excel
  3. Go to the Formulas tab of the ribbon.

    Formulas tab in Excel
  4. Select Lookup and Reference > INDEX.

    Index sub menu item in Formulas menu
  5. In the Select Arguments dialog box, choose array, row_num, column_num and select OK.

    array, row num, column num option in Select Arguments dialog

Enter the Function Arguments

  1. In the Function Arguments dialog box, choose the Array text box.

    Array line in Excel
  2. In the worksheet, highlight cells A2 to C4 to enter the range into the dialog box.

    Highlighted cells A2 to C4
  3. In the Function Arguments dialog box, select the Row_num text box.

    Row_num line in Function Arguments dialog box
  4. Enter 3.

    3 in the Excel dialog box
  5. Select the Column_num text box.

  6. In the worksheet, choose cell B7 to enter that cell reference in the dialog box.

    Cell B7 in Excel
  7. Select OK 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 (the selected range).

The word Gizmo in cell 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 consist of multiple nonadjacent ranges.

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 reference 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)

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 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) is used 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.

Enter the INDEX Function

Options for entering the function and its arguments include:

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

Although it is possible to type the complete function manually, it's easier to use the dialog box to enter the function arguments. The steps below use the dialog box to enter the function arguments.

Open 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 to open the INDEX function dialog box not present with other Excel functions.

To enter the INDEX function and arguments into cell B10 using the Function Arguments dialog box:

  1. Enter the data shown in this Excel worksheet.

    Data as shown in an Excel worksheet.
  2. Select cell B10 in the worksheet, which is where the function will be located.

  3. Go to Formulas.

  4. Select Lookup and Reference > Index.

  5. In the Select Arguments dialog box, select reference, row_num, column_num, area_num.

  6. Choose OK to open the Function Arguments dialog box.

Enter the Function Arguments

  1. In the Function Arguments dialog box, select the Reference text box.

  2. In the worksheet, highlight cells A1:E5 to enter that cell reference in the dialog box.

    Highlighted cells A1:E5
  3. In the Function Arguments dialog box, select the Row_num text box.

  4. In the worksheet, choose cell B7 to enter that cell reference into the dialog box.

    Cell B7 in Function Arguments dialog
  5. In the Function Arguments dialog box, select the Column_num text box.

  6. In the worksheet, choose cell B8 to enter that cell reference into the dialog box.

    Cell B8 in Excel
  7. In the Function Arguments dialog box, select the Area_num text box.

  8. In the worksheet, 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 arguments include:

  • The dialog box takes care of the function syntax making it easier to enter the function 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.