The INDEX function returns a value or the reference to a value from within a table or range. It is used to fetch values from tabular data when you have the row and column numbers of the lookup value.
The INDEX function in Excel have two formats, the Array Format (which is the most basic format), and the Range Format. In this tutorial, you will learn how to use both formats of the INDEX function in Excel.
The Array Format
The Array format of the Index function in Excel is used when you want to look up a reference to a cell within a single range. The syntax of the function is:
=INDEX (array, row_num, [col_num], [area_num])
array – Required. It is a range of cells, or an array constant. If the array contains only one row or column, the corresponding row_num or col_num argument is optional.If it has more than one row and more than one column, and only row_num or col_num is used, INDEX returns an array of the entire row or column in array.
row_num – Required. It is the position of the row from which the value is to be fetched. If omitted, col_num is required.
[col_num] – Optional. It is the column position in the reference or array. If omitted, row_num is required.
The value of an element in a table or an array, selected by the row and column number indexes.
- If both the row_num and col_num arguments are used, INDEX returns the value in the cell at the intersection of the two.
- If row_num and col_num are both set to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.
The Range Format
The Range format of the Index function can be used to extract references from ranges that are made up of more than one area. The syntax of the function is:
INDEX( range, row_num, [col_num], [area_num] )
Range – Required. The specified array or range of cells. If multiple areas are inserted directly into the function, the individual areas should be separated by commas and surrounded by brackets (ie. A1:B2, C3:D4, etc).
row_num – Required. It is the row number of the specified area. If set to zero or blank, this defaults to all rows of the specified area within the supplied range.
[col_num] – Optional. Denotes the column number of the specified area. If set to zero or blank, this defaults to all columns of the specified area within the supplied range.
[area_num] – Optional. It is the range in reference that should be used. If this argument is omitted, it defaults to the value 1 (i.e. the reference is taken from the first area in the supplied range.
Extract references from ranges that are made up of more than one area.
- After Reference and Area_num have selected a particular range, row_num and col_num select a particular cell: row_num 1 is the first row in the range, col_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of Row_num and Column_num.
- If you set row_num or column_num to 0 (zero), INDEX returns an array of the reference for the entire column or row, respectively.
- row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns the #REF! error. If row_num and col_num are omitted, INDEX returns the area in reference specified by area_num.
- The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value.
In the following examples, you will learn how to use the array format of the INDEX function.
Using the INDEX Function from the Dialog Box
To enter the INDEX function and arguments using the dialog box for the
- Click on cell E2, which is the location where the result will be displayed.
- 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 function’s dialog box.
- In the Select Arguments box, select the option that refers the array array, row_num, col_num.
- Highlight cells A2 to A8 in the worksheet to enter the array in the dialog box.
- Click on the row_num line in the dialog box.
- Insert the number 4 where Excel will fetch the value from.
- Click on the col_num line in the dialog box.
- Enter the number 2 on this line to return the value from column 2 which is Years of Experience.
- Click OK to complete the function and close the dialog box.
- The number 7 appears in cell E2 since the employee in row four, Sven Atkins has 7 years of experience.
When you click on cell E2 the complete function
) appears in the formula bar above the worksheet.
Index Function Excel (Array Format)
In the following example, assign the formula
=INDEX(A2:B7,4,2) to cell E2. The Index function returns a reference to row 4 of the range A2:B7, which is cell B5. This has the value 7.
Index Function Excel (Range Format)
In the following example, to find the Production hour of the month Jun, assign the formula
=INDEX((C2:D5,D7:E10,F2:G5),3,2,2) to cell G8.
The Index function returns a reference to row 2 and column 2 of the 3rd area in the supplied range. This is cell G8, which evaluates to the value 142.