Find Data in a List with the INDEX Function

01
of 02

Excel INDEX Function - Array Form

Find Data in a List with the Excel INDEX Function - Array Form
Find Data in a List with the INDEX Function - Array Form. © TedFrench

Excel INDEX Function Overview

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

There are two forms of the INDEX function available in Excel: the Array Form and the 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 column and row.

Excel INDEX Function - Array Form

An array is generally considered to be a group of adjacent cells in a worksheet. In the image above, the array would be 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 - Widget - found at the intersection point of row 3 and column 2.

The INDEX Function (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 Function (Array Form) Example

As mentioned, the example in the image above uses the Array form of the INDEX function to return the term Widget from the inventory list.

The information below covers the steps used to enter the INDEX function into cell B8 of the worksheet.

The steps make use of cell references for the Row_num and Column_num arguments, rather than entering these numbers directly.

Entering the INDEX Function

Options for entering the function and its arguments include:

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

Although it is possible to just 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 their 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. Click on cell B8 in the worksheet-  this is where the function will be located
  2. Click on the Formulas tab of the ribbon menu
  3. Choose  Lookup and Reference from the ribbon to open the function drop down list
  4. Click on INDEX in the list to bring up the Select Arguments dialog box - which lets you choose between the Array and Reference forms of the function
  5. Click on the array, row_num, column_num option
  6. Click on OK to open the INDEX function - Array form dialog box

Entering the Function's Arguments

  1. In the dialog box, click on the Array line
  2. Highlight cells A2 to C4 in the worksheet to enter the range into the dialog box
  3. Click on the Row_num line in the dialog box
  4. Click on cell B6 to enter that cell reference into the dialog box
  5. Click on the Column_num line in the dialog box
  6. Click on cell B7 to enter that cell reference into the dialog box
  7. Click OK to complete the function and close the dialog box
  8. The word Gizmo appears in cell B8 since it is the term in the cell intersecting the third row and second column of the parts inventory
  9. When you click on 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 function - Array form are:

#VALUE! - Occurs if either the Row_num, 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;
  • The Col_num argument is greater than the number of columns in the selected range.

Dialog Box Advantages

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

  1. 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.
  2. Cell references, such as B6 or B7, can be entered into the dialog box using pointing, which involves clicking on selected cells with the mouse rather than typing them in. Not only is pointing easier, it also helps to reduce errors in formulas caused by incorrect cell references.
02
of 02

Excel INDEX Function - Reference Form

Find Data in a List with the Excel INDEX Function - Reference Form
Find Data in a List with the INDEX Function - Reference Form. © TedFrench

Excel INDEX Function - 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 non-adjacent ranges as shown in the image above.

The INDEX Function (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) the cell references for the range of cells to be searched by the function for the desired information.

  • if multiple, non-adjacent 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  - the row number in the array from which to return a value.

  • optional for single row Reference ranges
  • required for single column and multiple row Reference ranges

Column_num - the column number in the array from which to return a value.

  • optional for single column Reference ranges
  • required for single row and multiple column Reference ranges

Note: 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) - if the Reference argument contains multiple non-adjacent 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 Function (Reference Form) Example

The example in the image above uses the Reference form of the INDEX function to return the month July from area 2 of the rage A1 to E1.

The information below covers the steps used to enter the INDEX function into cell B10 of the worksheet.

The steps make use of cell references for the Row_num, Column_num, and Area_num arguments, rather than entering these numbers directly.

Entering the INDEX Function

Options for entering the function and its arguments include:

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

Although it is possible to just 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 their 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 B10 using the function's dialog box.

  1. Click on cell B8 in the worksheet - this is where the function will be located
  2. Click on the Formulas tab of the ribbon menu
  3. Choose  Lookup and Reference from the ribbon to open the function drop down list
  4. Click on INDEX in the list to bring up the Select Arguments dialog box - which lets you choose between the Array and Reference forms of the function
  5. Click on the reference, row_num, column_num, area_num option
  6. Click on OK to open the INDEX function - Reference form dialog box

Entering the Function's Arguments

  1. In the dialog box, click on the Reference line
  2. Enter an open round bracket " ( " on this line in the dialog box
  3. Highlight cells A1 to A5 in the worksheet to enter the range after the open bracket
  4. Type a comma to act as a separator between the first and second ranges
  5. Highlight cells C1 to E1 in the worksheet to enter the range after the comma
  6. Type a second comma to act as a separator between the second and third ranges
  7. Highlight cells C4 to D5 in the worksheet to enter the range after the comma
  8. Enter a closing round bracket " ) " after the third range to complete the Reference argument
  9. Click on the Row_num line in the dialog box
  10. Click on cell B7 to enter that cell reference into the dialog box
  11. Click on the Column_num line in the dialog box
  12. Click on cell B8 to enter that cell reference into the dialog box
  13. Click on the Area_num line in the dialog box
  14. Click on cell B9 to enter that cell reference into the dialog box
  15. Click OK to complete the function and close the dialog box
  16. The month July appears in cell B10 since it is the month in the cell intersecting the first row and second column of the second area (range C1 to 1)
  17. When you click on cell B8 the complete function =INDEX((A1:A5,C1:E1,C4:D5),B7,B8) appears in the formula bar above the worksheet

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.
  • The Area_num argument is greater than the number of areas in the selected range.
Was this page helpful?