Limitations of INDEX-command

Kyytsis

New Member
Joined
Jan 28, 2005
Messages
2
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"... :oops:

Help Appreciated!!

Yours Truly,

Ville

Helsinki, Finland
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
You need to use INDIRECT

INDEX(INDIRECT(A1); MATCH(JAN);MATCH(001)) [ingore the input of match...).
 
Upvote 0
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
 
Upvote 0
cherria said:
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.

(y)

Splendid! Excellent.

Yours,

Ville
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top