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)

Points to note while using reference within Index function:

- Multiple ranges has to be separated with “,” and ranges have to be entered within ()
- All the ranges has to be within the same sheet

Index function is frequently used with MATCH function. If you notice, the row and column number is hardcoded – but in most of the cases with large data set, hard coding the numbers is impossible. Hence MATCH function comes handy and used with Index function.

I hope you like this section. Please help us with your valuable feedback. Thanks for reading!