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:

Index with Array

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)

Index with reference

Points to note while using reference within Index function:

  1. Multiple ranges has to be separated with “,” and ranges have to be entered within ()
  2. 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!

Leave a Reply

%d bloggers like this: