Index Worksheet Function

[gnaga]

Hi,

I am using Index Function in arrray formula.My formula is as follows

{=Index(A1:D3,A4)}

A4 is housing a number 2

I am entering this formula in the range of
E1:E4

It suppose to return the value of A2:D2 asper excel doumentation. But it is repeating the value of A2 in all the cells in the range of E1:E4.

If my understanding is right then why it is not returning the value? Otherwise what is it I am missing?

Please help me.

GNaga

 

[Aladin Akyurek]

In E1 enter & copy down as far as needed:

=INDEX($A$1:$D$3,$A$4,ROW(1:1))

 

[gnaga]

Aladin Can you please explain what is the wrong in my formula? Asper excel help documentation if we omit column number it suppose to return the entire row.

When I omit the row number the entire column values are returned in the target range.

Please explain me.

Thank you

GNaga

 

[Aladin Akyurek]

On 2002-09-29 06:59, gnaga wrote:
Aladin Can you please explain what is the wrong in my formula? Asper excel help documentation if we omit column number it suppose to return the entire row.

When I omit the row number the entire column values are returned in the target range.

Please explain me.

Thank you

GNaga

If you insist on an array-entered version, then you need to set up the formula as either

=INDEX(A1:D3,A4,)

or

=INDEX(A1:D3,A4,0)

and array-enter it in a 4-cell horizontal range.

If you insist having 4 values in row 2 of the original set in a vertical range, you need set up the formula as:

=TRANSPOSE(INDEX(A1:D3,A4,0))

and array-enter in a 4-cell vertical range.

What all this boils down to is that you need to transpose when you want to go from a horizontal to a vertical range (or vice versa).

Note that what I proposed in my initial reply is not an array formula.

EDIT for typo...
This message was edited by Aladin Akyurek on 2002-09-29 07:54