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.
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.
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.
INDEX has returned 1904 in the result.
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.
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.
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.
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.