Excel INDEX Function

HomeExcel Functions List (Top 100) Examples + Sample FileExcel INDEX Function (Example + Sample File)

What is EXCEL INDEX FUNCTION

The Excel INDEX Function is listed under Microsoft Excel's Lookup Functions category. It returns a value from a list of values based on its index number. In simple words, INDEX returns a value from a list of value and you need to specify that value's position.

How to use it

To learn how to use the INDEX function in Excel, you need to understand its syntax and arguments:

Syntax

INDEX has two different syntaxes.

In the first, you can use an array form of an index to simply get a value from a list using its position.

INDEX(array, row_num, [column_num]) 

In the second, you can use a referral form that is less used in real life but you can use it if you have more than one range to get value from.

INDEX(reference, row_num, [column_num], [area_num])

Arguments

  • array: A range of cells or an array constant.
  • reference: A range of cells or multiple ranges.
  • row_number: The number of the row from which you want to get the value.
  • [col_number]: The number of the column from which you want to get the value.
  • [area_number]: If you are referring to more than one range of cells (using reference syntax), specify a number to refer to a range from all those.

Notes

  • When both the row_num and column_num arguments are specified, it will return the value in the cell at the intersection of both.
  • If you specify row_num or column_num as 0 (zero), it will return the array of values for the entire column or row, respectively.
  • When row_num and column_num are out the range, it will return an error #REF!.
  • If area_number is greater than the number ranges you have specified then it will return #REF!.

Example

To master the INDEX function we need to try it out in an example, so make sure to check out the below one:

1. Using ARRAY – Getting Value from a List

In the below example, we have used the INDEX function to get the quantity of June month.

In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number. INDEX has returned the value 1904 in the result.

And if you referring to a range with more than one column you have to specify the column number.

2. Using REFERENCE – Getting Value from Multiple Lists

In the below example, instead of selecting all the range in one go, I have selected it as three different ranges.

In the last argument, we have specified 2 in area_number which will define the range to use from these three different ranges.

Now in the second range, we are referring to the 5th row and 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.

Related functions

This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.