Syntax (Array Version). INDEX(array,row_num,column_num)
Syntax (Reference Version). INDEX(reference,row_num,column_num,area_num)
If the row_num and column_num arguments exceed the range, you will get the #REF!
error. The formula
=INDEX(B4:C6,3,2)
returns the element in C6 (third row, second column in the range B4:C6). Similarly, consider the following:
=INDEX({2;4;6;8},2,1)
(This isn’t an array formula, so you have to enter the braces instead of pressing Ctrl+Shift+Enter.) The argument {2;4;6;8} is recognized as column, and the formula returns 4
(the second row in the column).
Array constants that include multiple columns constitute a special situation, because rows are separated by semicolons, and columns are separated by commas. For example, {11,12,13;21,22,23} is recognized as
|
|
|
|
|
|
The formula
=INDEX({11,12,13;21,22,23},2,3)
returns 23
(second row, third column).
You can use the INDEX() function in an array formula as shown in the following example. If you enter
{=INDEX({11,12,13;21,22,23},0,3)}
or
{=INDEX({11,12,13;21,22,23},,3)}
as array formula in two vertical cells, the formula returns 13, 23
. If you enter
{=INDEX({11,12,13;21,22,23},2,0)}
or
{=INDEX({11,12,13;21,22,23},2)}
as array formula in three horizontal cells, the formula returns 21, 22, 23
. If the destination range is larger than the source range, the extra cells show the #N/A
error.
In the reference version, the first argument must be a reference. If you want to use multiple ranges, each range must consist of contiguous cells. The reference argument for multiple references separated by semicolons must be enclosed in parentheses to ensure that Excel assigns the arguments correctly.
The order of the references in the argument is indicated by an integer, starting at 1, to identify the cell range in the area_num argument. The row_num and column_num arguments behave as in the array version.
If one of the row_num, column_num or area_num arguments exceeds the range, the function returns the #REF!
error. The formula
=INDEX((B18:C20,E18:G19),3,2,1)
returns the reference to the element in the third row in the second column in the first range (C20).
INDEX() accepts named ranges in the reference argument. If you enter the name first for range B18:C20 (by selecting Defined Names/Define Name on the Formulas tab in Excel 2007 or Excel 2010 or Insert/Names/Define in earlier versions) and the name second for range E18 through G19, the formula
=INDEX((first,second),2,1,2)
returns a reference to the cell in the second row in the first column of the range named second: E19
. You can also name all of the cells B18:C20,E18:G19 (in this order) with the name both. Then the formula
=INDEX(both,2,3,2)
returns the information in the third column in the second row of the second subarea: G19
. You can omit the row_num or column_num argument or both (leaving the space between the commas empty) to reference columns or rows or an entire range. In this case, you have to use the formula as array formula or you will get the #VALUE!
error unless the range consists of only one row or column.
Examples. The following examples show how this function is used.
Finding Information. This example demonstrates the reference version of the function. Assume that you have divided an advanced training course into three parts and offer single-unit or complete conference reservations. You also offer an early-bird discount for participants who book before a deadline. The details are shown in Figure 10-4.
=INDEX((D47:D50,E47:E50),VLOOKUP(C53,B47:C50,2,FALSE),,IF(C54<C52,1,2))
You divide the price range into two parts (don’t forget to put the reference in parentheses), calculate the course element with VLOOKUP(), and use this number as the row index. You don’t need a column index, because each range includes only one column. The IF() function compares the booking date with the deadline to determine the range to be searched.
=INDEX((NumberOne,NumberTwo),ROWS(NumberOne),COLUMNS(NumberOne),1)+ INDEX((NumberOne,NumberTwo),ROWS(NumberTwo),COLUMNS(NumberTwo),2)
as well as the array version
=INDEX(NumberOne,ROWS(NumberOne),COLUMNS(NumberOne))+ INDEX(NumberTwo,ROWS(NumberTwo),COLUMNS(NumberTwo))