Returns a value from a table, given a row and column number.

INDEX(datatable, row, column, areanumber)

returns the value at position row, column in the range or array datatable.

If datatable has multiple ranges then areanumber specifies which is to be used. areanumber is optional and defaults to 1.

If datatable has a single column, this syntax may be used:

INDEX(datatable, row).

If datatable has a single row, this syntax may be used:

INDEX(datatable, column).

If cells B1, B2, B3 contain 5, red and 6 and cells C1, C2, C3 contain TRUE, 3 and blue:-

INDEX(B1:C3, 1, 1)

returns 5.

INDEX(B1:C3, 3, 2)

returns blue.

INDEX(B1:B3, 2)

returns red.

INDEX(B1:C2, 1, 2)

returns TRUE.

INDEX(B3:C3, 2)

returns blue.

Like other functions, INDEX may be used in an array formula context. When entered as an array formula, the row, column, and areanumber parameters — which are expected to be scalars — can be supplied as arrays instead.

This example shows a row parameter supplied as a two-row vector, and the column and areanumber parameters as non-zero scalars. These parameters ask INDEX to return a reference to a single cell. The array context expands this to multiple rows with a single column.

INDEX({1,3,5|7,9,10},{2|1},1,1), entered as an array formula

returns array {7|1}, with two rows and one column.

Here instead, the row and areanumber parameters are non-zero scalars, and the column parameter is zero. These parameters ask INDEX to return a reference to all columns of one row in the datatable. The array context expands this to a result to enough cells to hold all columns of the row.

INDEX({1,3,5|7,9,10},2,0,1), entered as an array formula

returns array {7, 9, 10}, with one row and three columns.

N.B. If you both use an array parameter and ask for a complete row to be returned, then all but the first cells of the row are returned as

INDEX({1,3,5|7,9,10},{2|1},0,1), entered as an array formula

returns array {7, #VALUE!, #VALUE! | 1, #VALUE!, #VALUE!}, with two row and three columns, but errors in place of expected values.

PRODUCT & FEATURES

CAPTCHA Protection

RESOURCES

© 2023 Zapof