What is the Excel INDEX Function?
The Excel INDEX function returns a value from a range or array based on a given row and column position.
The INDEX function is usually used with the MATCH function, where MATCH role to find and feeds a position that will be used by the INDEX function
INDEX(array, row_num, [column_num])
array, required, a range or an array containing data
row_num, required, the row position in range or array from which to return a value. row_num could be omitted, and column_num becomes required.
column_num, optional, the column position in range or array from which to return a value. column_num could be omitted, and row_num becomes required.
- INDEX function returns #REF! error if row_num or column_num greater than array dimension.
- INDEX function returns a value at the intersection of row_num and column_num when both of arguments are used.
- INDEX function returns a range of an entire row or column in the array when only row_num or column_num is used.
- Two ways to use the INDEX function, array form or reference form. See “How to Use INDEX Function in Excel” below
How to Use INDEX Function in Excel
For example, there is data as shown below. How does the INDEX function result in the column E?
INDEX Function #1
The formula above uses all argument. The return value is in the intersection of row 1 column 1, cell A1.
The result is 54.
INDEX Function #2
The column_num argument is larger than the array dimension. The result is a #REF! error.
INDEX Function #3
Just like the INDEX function #2, the row_num argument is greater than the array dimension. The result is a #REF! Error.
INDEX Function #4
You can use the INDEX function in the reference form and need another excel function to process the results
The formula INDEX(A1:A10,10) point to cell A10. If you use the INDEX formula like it, you got the content of cell A10, but if you use with the SUM function like the formula above the result is A10, a cell reference.
So the formula =SUM(A1:INDEX(A1:A10,10)) is the same as the formula =SUM(A1:A10).
The result is 412
INDEX Function #5
If you use row_num or column_num argument only, then INDEX function returns an array of an entire row or column in the array
The INDEX function in the formula above ignores the row_num argument (zero number/0), so the INDEX function returns a range of the first column of an array, range A1:A10.
So the formula =SUM(INDEX(A1:C10,0,1)) is the same as the formula =SUM(A1:A10).
The result is 412
INDEX Function #6
Like INDEX function #2 and #3, if the row_num or column_num argument is greater than the array dimension, it will return a #REF! error, even if it’s in the reference form.
Please see the image below for the complete results of the INDEX function in column E.