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:
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])
- 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.
- 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!.
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.
This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:
- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HLOOKUP Function
- EXCEL HYPERLINK Function
- EXCEL INDIRECT Function
- EXCEL LOOKUP Function
- EXCEL MATCH Function
- EXCEL OFFSET Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function
- EXCEL VLOOKUP Function
About the Author
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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.