index lookup and array constant

[Nicawette]

Hi all,

I've the following formula :

=INDEX(Routing!$E$8:$F$12;(RANDBETWEEN(1;5));(RANDBETWEEN(1;2)))

which works in the right way no problem at all

but I want to have this formula more dynamic/automatic and change the
"Routing!$E$8:$F$12" with a lookup function (eg Vlookup); something
like that :

=INDEX(Vlookup(A1; B1:C8;2);(RANDBETWEEN(1;5));(RANDBETWEEN(1;2)))

but the problem is that the array in the index formula is an array
constant and doesn't accept formulas.

Do you know if a workaround is possible ?

Thank you for the support

Tx Nic.

 

[willwonka]

You want your Range to be more dynamic? Have you considered using a
dynamic Range name via an Offset formula?

 

[Max]

Think something like this using INDIRECT would work:
=INDEX(INDIRECT("'Routing'!E8:"&VLOOKUP(A1,
B1:C8,2)),RANDBETWEEN(1,5),RANDBETWEEN(1,2))

Above assumes that the C1:C8 holds the variable cell refs such as: F8,
G20, K100, etc which are to be appended to form the range:
'Routing'!E8:xx. The VLOOKUP returns the cell ref. Change the commas in
the formula to semicolons to suit your continental version.

 

[Nicawette]

Thanks max for your help it works as expected cool

regards

nic

Max a écrit :

 

[Max]

You're welcome, Nic.
Thanks for call back ..