INDEX() Reference Syntax

[Expiry]

I've written an INDEX formula which is using 1 cell as its reference. If you read the instructions in Excel it says that you can omit the Area number if you only have 1 reference, which I do, but it doesn't work when I leave this out. It does work when I put a 1 as the area.

Does anyone know why?

 

[GlennUK]

Show your formula, and describe the references and values that you use in it. And tell us all in what way it doesn't work for you.

 

[Expiry]

Hmmm, the Area bits eems to be working now. I must have been doing something else wrong. However, I still don't understand why the reference syntax doesn't work.

This is the formula that works.

=INDEX(A1:A1000,COUNTA(D:D),1,1)

This also now works:

=INDEX(A1:A1000,COUNTA(D:D),1)


The following doesn't work - I get a #REF! - but the Excel instructions seem to say that it should work:

=INDEX(A1,COUNTA(D:D),1)

 

[GlennUK]

Explain what you think:
=INDEX(A1,COUNTA(D:D),1)
should be doing?

If COUNTA(D:D) is more than 1 it will return a #REF! error. Are you saying it should do something else?

 

[Expiry]

I thought the reference bit (A1) is just giving me a starting point, rather than me having to pick a range for it to count within.

If it doesn't allow that, what's the point of the reference style over the array style?

 

[GlennUK]

... what's the point of the reference style over the array style?

Explain what you mean by reference style and array style.

EDIT: OK I know what you mean ... explain why you think they can be compared as one being used instead of another ... the styles are used depending on what you are dealing with.

 

[GlennUK]

In fact, to put it simply, only supply the area if you have more than one area.

 

[Expiry]

Explain what you mean by reference style and array style.

EDIT: OK I know what you mean ... explain why you think they can be compared as one being used instead of another ... the styles are used depending on what you are dealing with.

Hi, Glenn. Obviously I don't understand the difference.

If I used the array style, I had to name a range, in this case A1:A1000. But that's not really the range, I just picked that as a range that's definitely bigger than I need.

I thought if I used the reference style, I could just say that I wanted to start in A1, because that's the only cell that's relevant. The rest of the index formula tells it where to move to from there.

If not, the why are there the two styles?

 

[GlennUK]

Well, maybe the full help may shed some light on this:

Array form
Description
Returns the value of an element in a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), selected by the row and column number indexes.

Use the array form if the first argument to INDEX is an array constant.

Syntax
INDEX(array, row_num, [column_num])The INDEX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Array Required. A range of cells or an array constant.
If array contains only one row or column, the corresponding row_num or column_num argument is optional.
If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
Row_num Required. Selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.
Remark
If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num.
If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER.
Note In Excel Web App you cannot create array formulas.

Row_num and column_num must point to a cell within array; otherwise, INDEX returns the #REF! error value.

Reference form
Description
Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

Syntax
INDEX(reference, row_num, [column_num], [area_num])The INDEX function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Reference Required. A reference to one or more cell ranges.
If you are entering a nonadjacent range for the reference, enclose reference in parentheses.
If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).
Row_num Required. The number of the row in reference from which to return a reference.
Column_num Optional. The number of the column in reference from which to return a reference.
Area_num Optional. Selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.
For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4.
Remark
After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of row_num and column_num.
If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
Row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns the #REF! error value. If row_num and column_num are omitted, INDEX returns the area in reference specified by area_num.
The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.
Note The CELL function is not available in Excel Web App.

 

[Expiry]

Ok, it looks like the only difference is that with Reference you can pick from more than one range in the same formula, say you had the same set up of data for 3 different months, you could set up the area number to return 1,2 or 3 based on month chosen and apply the same index formula to whichever range that then points you to.

But if you're only using one range, then there's no difference whatsoever.

the example shown above for Reference style:

CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1).

applies equally to the Array style.