How to use the INDEX function in excel?

1. Excel Index function as an array

The Index function is an array when the first argument of the function is an array constant.With this function, we have the following formula:

INDEX (Array, Row_num, [Column_num])

Inside:

+ Array: a range of cells or some required array of arrays.

+ Row_num: select rows in the array from which to return a value.

+ Column_num: select the column in the array from which to return a value.

Note readers must have at least one of the two arguments Row_num and Column_num

In the following example, we have the item name and the unit price of each item, find the unit price of the item knowing it's in row 2 column 2

How to use the INDEX function in excel? Picture 1

Step 1:

In cell C7, you will enter the formula according to the above syntax, and then we press Enter to perform the Index function

E9: = INDEX (D5: E7,2,2)

How to use the INDEX function in excel? Picture 2

Step 2:

Soon we will be returned to the position value E9 corresponding to the ceiling fan unit cost of 235000

How to use the INDEX function in excel? Picture 3

2. Excel Index function as a reference

The Index reference function returns the reference of a cell at the intersection of a specific row and column.We have the reference Index formula as follows:

INDEX (Reference, Row_num, [Column_num], [Area_num])

Inside:

+ Reference: required reference region.

+ Row_num: the row number from which to return a reference, required.

+ Column_num: the column number from which to return a reference, optional.

+ Area_num: number of range will return the value in Reference.If Area_num is omitted, the INDEX function uses region 1, optional.

Step 1:

Also with the above Excel data sheet, we enter the formula as below.Then also press Enter.

Click at cell E9: = INDEX (D5: E7,2,1,1)

How to use the INDEX function in excel? Picture 4

Step 2:

The result returned will be the name of the product type 'FAN CEILING'

How to use the INDEX function in excel? Picture 5

=> Thus, there are 2 ways to use the INDEX function as array and reference form. Depending on the requirements of each article, the reader has a suitable way to use. Not too hard, right? In addition, you can combine the INDEX function with the MATCH function to be instructed in the following article of TipsMake