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

Select cell B8 in the worksheet. This is where the function will be located.

Choose the Formulas tab of the ribbon.

Select Lookup and Reference to open dropdown list.

Choose INDEX to bring up the Select Arguments dialog box where you choose between the Array and Reference forms of the function.

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

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

Highlight cells A2 to C4 in the worksheet to enter the range into the dialog box.

Select the Row_num line in the dialog box.

Choose cell B6 to enter that cell reference into the dialog box.

Select the Column_num line in the dialog box.

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

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

Select cell B8 in the worksheet, which is where the function will be located.

Choose the Formulas tab of the ribbon.

Select Lookup and Reference from the ribbon to open the function dropdown list.

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.

Select the reference, row_num, column_num, area_num option.

Choose OK to open the INDEX function  Reference form dialog box.
Entering the Function's Arguments

In the dialog box, select the Reference line.

Enter an open round bracket " ( " on this line in the dialog box.

Highlight cells A1 to A5 in the worksheet to enter the range after the open bracket.

Type a comma to act as a separator between the first and second ranges.

Highlight cells C1 to E1 in the worksheet to enter the range after the comma.

Type a second comma to act as a separator between the second and third ranges.

Highlight cells C4 to D5 in the worksheet to enter the range after the comma.

Enter a closing round bracket " ) " after the third range to complete the Reference argument.

Select the Row_num line in the dialog box.

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

Select the Column_num line in the dialog box.

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

Select the Area_num line in the dialog box.

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

Select OK to complete the function and close the dialog box.
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).
When you select 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; 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.