How to Use Excel INDEX Function

how to use excel index function

Quick Intro

It happens sometimes when you want to get a value from a list and you know the position of that value from the list. Excel INDEX Function can help you to do that.

By using INDEX Function you can fetch a value from a range of cells in which you know the exact position of the value.

In the below example, we have a month wise quantity. And, we have used index function to get quantity for the month of Jun as in the list Jun is on 6th.

how to use excel index function get value from a list

Syntax

Excel INDEX Function has two different syntaxes.

Array – You can use array form of index to simply get a value from a list using its position. INDEX(array, row_num, [column_num])

Reference – Reference form is less used in real life but you can use it if you have more than one range to get value from (I’ll show further, an example to make it understand). INDEX(reference, row_num, [column_num], [area_num])

  • array – A range of cells or an array constant.
  • reference – A range of cells or multiple ranges.
  • row_number – Number of the row from which you want to get the value.
  • [col_number] – 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.

More Information About Excel Index Function

  • 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!.

Quick Tip: If you use INDEX Function as a range reference [A1:Index(B1:B5,1,1)] it will return cell reference instead of value.

Examples

Here I have listed some easy to follow examples for both of the forms of INDEX function.

1. Using ARRAY – Getting Value from a List

In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.

And, I’m only referring to a single column that’s why I have specified 1 in column_number.

how to use excel index function example with arrays

INDEX has returned 1904 in the result.

how to use excel index function get value from a list

And, if you want, to get any other value, all you have to do, just mention the position of the value in the list. Whereas, if you referring to a range with more than one column you have to specify the column number.

how to use excel index function example with arrays column row intersect

INDEX will return the value from the intersection of row_number and column_number.

2. Using REFERENCE – Getting Value from Multiple Lists

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

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

how to use excel index function using as reference

Now, in the second range, I’m referring to 5th row & 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.

You can also refer to ranges with multiple columns.

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel INDEX Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have CHOOSE function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.