How to Use the INDEX Function in Excel?: Step-by-Step Guide

1. Excel Index Function as an Array

INDEX(Array, Row_num, [Column_num])

Arguments

+ 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

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)

Step 2:

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

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])

Arguments

+ 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)

Step 2:

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

? 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 this guide of TipsMake

social shareand social share