Index function in Excel

Hello all. In this section, let’s explore the Index function. Index is a commonly used function that returns the value of cell at the intersection of specified row and column. In a given range if row and column number is provided, the function returns the value at the intersection.

Syntax:

=Index(array,row_num,[column_num])

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

From the syntax you might have realized we can use Index in two ways – using array or reference. Let’s look both in this section.

Index with Array

We shall be using the below dataset. If we need to get the value of cell at 4th row and 2nd column:

In this formula, =INDEX(A2:D11,4,2), the array given is the entire data set of sales data for employee. The row number given 4 and column number given is 2 – the function moves 4 rows down and moves 2 columns right and returns the value.

Index with Reference

Reference is used when you have multiple ranges to look at. In the below data set, let’s look at multiple reference ranges – eg., range 1 as A2:D5, range 2 as A7:D11, 3rd as A14:D21. Then specify the row and column number and specify which range out of the 3 ranges to use.

In the above formula, we declare 4 different ranges as the reference attribute, row and column as 2 and 3. Then we specify 2 as the area_num which tells the function to look at the 2nd range (A7:D11)