Limitations of INDEX-command

[Kyytsis]

Hi,

What would be the best way to get the array changed when using a index command?

INDEX(array;row_num;column_num)

As arrays I have data on other sheets and I use MATCH-command to look up correct account (row) and month information (column). The problem is, that as different years are on different sheets I can't change the year as Excel doesn't seem to allow the use of cell-reference as array.

For example: I want to get the number from 2003 january from account 001. All these infos are selected from data validation boxes. If the year 2003 corresponds to a named are DATA1 and is written on cell A1, the input is something like:
INDEX(A1; MATCH(JAN);MATCH(001)) [ingore the input of match...).

This doesn't work, if I manually write DATA1 as array, it works...

Wouldn't be a problem if macros would be allowed, but those are "against company policy"...

Help Appreciated!!

Yours Truly,

Ville

Helsinki, Finland

 

[cherria]

You need to use INDIRECT

INDEX(INDIRECT(A1); MATCH(JAN);MATCH(001)) [ingore the input of match...).

 

[Richie(UK)]

Hi,

Welcome to the board.

Try using the Indirect function. For example, this will sum the values in DATA1 when that name is written in cell A1:

=SUM(INDIRECT(A1))

HTH

 

[Kyytsis]

You need to use INDIRECT

INDEX(INDIRECT(A1); MATCH(JAN);MATCH(001)) [ingore the input of match...).

Thanks Cherria! (and Richie!)

This works, now I can get rid of all the IF-commands used to reroute.



Splendid! Excellent.

Yours,

Ville

 

[jindon]

Hi

I think this is typical combination use of INDEX and INDIRECT function.

The idea is to name each lookup table as well as titles row and dolumn for the MATCH function.

Please refer to Excel help for details. hard to say anything without the structure of your sheet.

hope this helps

jindon

ooooops seems already solved

 

[fairwinds]

Hi,

You can also specify several ranges directly in the index function and use the last condition to choose inbetween them.

E.g.

INDEX((A1:C6,A8:C11),2,2,2)

Check it out in help file