Index function in Excel

The Index function is a function that can refer to any cell on an Excel worksheet, it is combined with many other functions and brings a significant effect.

Index function in Excel Picture 1

To make full use of the Index function, you should first know the basic syntax and usage of the function to later use the Index function more effectively. You follow the article below, the article syntax instructions and the use of the Index function form.

Description

The Index function returns the value of an element either referring to a value in the table or in an array via that element's row and column index.

There are two types of Index functions:

- Array form: returns the value of an element in the table or in the specified array.

- Reference type: returns the reference of the specified cell.

Array form

Description:

This form returns the value of an element in a table or in an array via row and column index. Use the array form if the first argument of the Index function is the array constant.

Syntax:

INDEX (array, row_num, [column_num])

Inside:

- array: is the range of cells or an array constant, array is the required argument:

+ If array contains a row or a column, the corresponding row_num or column_num argument is optional.

+ If array contains many rows and columns but only the row_num or column_num argument is used, the index function will return the array with the entire row or entire column in the array.

- row_num: the number of rows in the array from which a value is returned.

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

Attention:

- Must have at least one of the arguments row_num or column_num.

- If both row_num and column_num are used, the function will return the value in the intersection cell of row_num and column_num.

- The two arguments row_num and column_num must point to a cell in the array, otherwise the Index function will return an error value.

- If you enter the index row_num or column_num is 0, the function will return the value array for the entire column or corresponding row.

Example: The following table is available:

Index function in Excel Picture 2

- Value at row 2 column 3: = INDEX (B4: E7,2,3)

Index function in Excel Picture 3

Reference form

Description:

Returns a cell reference specified by a specific row and column index.

Syntax:

= INDEX (reference, row_num, [column_num], [area_num])

Inside:

- reference: required reference region, reference to one or more different cell ranges.

+ If you enter non-adjacent reference ranges, the references must be enclosed in parentheses.

+ If each reference contains only one row or one column, then the corresponding row_num or column_num is optional.

- row_num: the row index in the reference from which to return a reference, required.

- column_num: the column number in the reference from which to return an optional, reference.

- area_num: range in reference from which to return the value of row_num and column. For example, select the first area, enter 1, the second area then enter 2 . if area_num is not entered, the default value is area 1, optional argument.

Note:

- Row_num, column_num and area_num must point to the same cell in the reference otherwise the function will return an error value.

- If you enter row_num or column_num as 0, the function will return the reference for the entire column or the corresponding row.

- The returned result of the Index function is a reference and depending on the formula, the value returned by the function can be used as a value or a reference.

For example: Given 2 reference regions are the following 2 data tables:

Index function in Excel Picture 4

- The value in row 2 column 3 in region 1: = INDEX ((B4: E7, B9: D11), 2,3,1)

Index function in Excel Picture 5

So you know how to use the index function in the simplest way, in the process of using it, you will need to combine the Index function with other functions to use the function more effectively. Good luck!