INDEX: Returning entire rows/columns Reply

If either (or sometimes both – see below) of the row or column parameters is zero, or omitted, and the INDEX is properly coerced (e.g. forms part of a larger formula), a reference to the entire specified column or row is generated. Note that the return here is not a single value, but rather an array of values.

We are not so much interested in returning this array in an actual cell somewhere (which in any case we can’t do), but more in being able to reference/manipulate that array in further calculations. For example:

=INDEX(A1:C3,0,2)

or

=INDEX(A1:C3,,2))

returns a reference to the range B1:B3.

And:

=INDEX(A1:C3,3,0)

or

=INDEX(A1:C3,3,)

returns a reference to the range A3:C3.

(Note that, as stated above, it is assumed that the array resulting from this INDEX construction is being passed to some other function in order to coerce this array of returns. Technically, on its own, INDEX(A1:C3,0,2) means something else entirely – anyone wishing to experiment with the union property of functions may like to investigate further.)

If the single column or row generated is not an actual range reference, but e.g. the result of some Boolean test on a range (see here for more details of using INDEX as an alternative to array-entry), for example in the construction:

=MATCH(TRUE,INDEX(A1:A10<>"",,1),0)

then it is not strictly necessary to include a row or column parameter: since you have only one anyway, Excel defaults to that by assumption, such that:

=MATCH(TRUE,INDEX(A1:A10<>"",),0)

for example, which technically has zero for the row parameter and no column parameter (beware: the two are not the same!), is a syntactically valid alternative.

Just to add to the confusion, entering zero for (or again, omitting) both parameters is an equally valid (though not strictly necessary) alternative for returning an entire row or column, i.e.:

=MATCH(TRUE,INDEX(A1:A10<>"",0,0),0)

or

=MATCH(TRUE,INDEX(A1:A10<>"",,),0)

I actually tend to prefer this last variation. Despite it being technically unnecessary, the two commas serve to emphasize this property of INDEX and so help (me at least) avoid unwanted errors in formulas. Of course, the alternatives are all perfectly valid, and many may feel inclined to one or the other, but for future posts I will use exclusively the double-omission variation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s