Excel INDEX Function

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 Syntax

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.

Usage Notes:

• 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?

Array Form

INDEX Function #1

=INDEX(A1:C10,1,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

=INDEX(A1:C10,5,4)

The column_num argument is larger than the array dimension. The result is a #REF! error.

INDEX Function #3

=INDEX(A1:C10,11,2)

Just like the INDEX function #2, the row_num argument is greater than the array dimension. The result is a #REF! Error.

Reference Form

INDEX Function #4

=SUM(A1:INDEX(A1:A10,10))

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

=SUM(INDEX(A1:C10,0,1))

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

=SUM(INDEX(A1:C10,0,4))

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.

INDEX Example

Another Lookup & Reference Function