### Excel INDEX Function - Array Form

### 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:

- Typing the complete function:
*=INDEX(A2:C4,B6,B7)*into cell B8 - 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.

- Click on cell B8 in the worksheet- this is where the function will be located
- Click on the
*Formulas*tab of the ribbon menu - Choose
*Lookup and Reference*from the ribbon to open the function drop down list - 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 - Click on the
*array, row_num, column_num*option - Click on OK to open the INDEX function - Array form dialog box

### Entering the Function's Arguments

- In the dialog box, click on the
*Array*line - Highlight cells A2 to C4 in the worksheet to enter the range into the dialog box
- Click on the
*Row_num*line in the dialog box - Click on cell B6 to enter that cell reference into the dialog box
- Click on the
*Column_num*line in the dialog box - Click on cell B7 to enter that cell reference into the dialog box
- Click OK to complete the function and close the dialog box
- 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 - 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:

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

### Excel INDEX Function - Reference Form

### 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:

- Typing the complete function:
- 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.

- Click on cell B8 in the worksheet - this is where the function will be located
- Click on the
*Formulas*tab of the ribbon menu - Choose
*Lookup and Reference*from the ribbon to open the function drop down list - 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 - Click on the
*reference, row_num, column_num, area_num*option - Click on OK to open the INDEX function - Reference form dialog box

### Entering the Function's Arguments

- In the dialog box, click on 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 - Click on the
*Row_num*line in the dialog box - Click on cell B7 to enter that cell reference into the dialog box
- Click on the
*Column_num*line in the dialog box - Click on cell B8 to enter that cell reference into the dialog box
- Click on the
*Area_num*line in the dialog box - Click on cell B9 to enter that cell reference into the dialog box
- Click OK to complete the function and close the dialog box
- 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) - 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.